Quote:
Originally Posted by wutamess
I'm getting a debug error in this code: At th e End with statement. I hit continue and it continues and processes the next section but the debug error is killing me.
Basicallym I'm trying to sort 2 different sets of columns inside the same actice sheet.
Sub SortBEandGJ()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+n
'
Columns("B:E").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B100"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("B1:E100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("G:J").Select
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("G2:G100"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("G1:J100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A2").Select
End Sub
|
The debug error you're encountering is likely due to the incorrect use of the Sort object. The Sort object is used to sort a range of data, but in your code, you're trying to sort columns directly without specifying a range.
Here's the corrected version of your code:
Sub SortBEandGJ()
Dim ws As Worksheet
Dim sortRange As Range
Set ws = ActiveWorkbook.ActiveSheet
' Sort columns B to E
Set sortRange = ws.Range("B1:E100")
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=sortRange.Columns(1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Sort columns G to J
Set sortRange = ws.Range("G1:J100")
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=sortRange.Columns(1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Range("A2").Select
End Sub
/also Chat GPT
//hope this info getting to you 12.5 years later wasn't too inconvenient