ExcelBanter - vba sort range problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - vba sort range problem (https://www.excelbanter.com/excel-programming/372607-vba-sort-range-problem.html)

ADK

vba sort range problem

Beginner at vba.
Get error at line: GroupIt = "B3:E" + LastRow

Example of what I am trying to do: say last row is 36 this time around, so
GroupIt would = B3:E36

What code can perform this correctly?

Sub SortGroup()
Dim GroupIt As Range
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
GroupIt = "B3:E" + LastRow
Range(GroupIt).Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' ,
DataOption1:=xlSortNormal

End Sub

Bob Phillips

vba sort range problem

GroupIt is a range so you cannot set it to a string. Anyway, you don't need
it

Sub SortGroup()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
Range("B3:E" + LastRow).Sort Key1:=ActiveCell, _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom ' , _
DataOption1:=xlSortNormal

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ADK" wrote in message
...
Beginner at vba.
Get error at line: GroupIt = "B3:E" + LastRow

Example of what I am trying to do: say last row is 36 this time around, so
GroupIt would = B3:E36

What code can perform this correctly?

Sub SortGroup()
Dim GroupIt As Range
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
GroupIt = "B3:E" + LastRow
Range(GroupIt).Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' ,
DataOption1:=xlSortNormal

End Sub

Mike Fogleman

vba sort range problem

GroupIt = Range("B3:E" & LastRow)

Mike F

"ADK" wrote in message
...
Beginner at vba.
Get error at line: GroupIt = "B3:E" + LastRow

Example of what I am trying to do: say last row is 36 this time around, so
GroupIt would = B3:E36

What code can perform this correctly?

Sub SortGroup()
Dim GroupIt As Range
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
GroupIt = "B3:E" + LastRow
Range(GroupIt).Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' ,
DataOption1:=xlSortNormal

End Sub

ADK

vba sort range problem

I get an error at the Range(:B3...... line

"Bob Phillips" wrote in message
...
GroupIt is a range so you cannot set it to a string. Anyway, you don't
need
it

Sub SortGroup()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
Range("B3:E" + LastRow).Sort Key1:=ActiveCell, _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom ' , _
DataOption1:=xlSortNormal

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ADK" wrote in message
...
Beginner at vba.
Get error at line: GroupIt = "B3:E" + LastRow

Example of what I am trying to do: say last row is 36 this time around,
so
GroupIt would = B3:E36

What code can perform this correctly?

Sub SortGroup()
Dim GroupIt As Range
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
GroupIt = "B3:E" + LastRow
Range(GroupIt).Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' ,
DataOption1:=xlSortNormal

End Sub

Bob Phillips

vba sort range problem

Sorry, the + should be & there.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
GroupIt is a range so you cannot set it to a string. Anyway, you don't
need
it

Sub SortGroup()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
Range("B3:E" + LastRow).Sort Key1:=ActiveCell, _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom ' , _
DataOption1:=xlSortNormal

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ADK" wrote in message
...
Beginner at vba.
Get error at line: GroupIt = "B3:E" + LastRow

Example of what I am trying to do: say last row is 36 this time around,
so
GroupIt would = B3:E36

What code can perform this correctly?

Sub SortGroup()
Dim GroupIt As Range
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
GroupIt = "B3:E" + LastRow
Range(GroupIt).Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' ,
DataOption1:=xlSortNormal

End Sub

ADK

vba sort range problem

thanks!!!

"Bob Phillips" wrote in message
...
Sorry, the + should be & there.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
GroupIt is a range so you cannot set it to a string. Anyway, you don't
need
it

Sub SortGroup()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
Range("B3:E" + LastRow).Sort Key1:=ActiveCell, _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom ' , _
DataOption1:=xlSortNormal

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ADK" wrote in message
...
Beginner at vba.
Get error at line: GroupIt = "B3:E" + LastRow

Example of what I am trying to do: say last row is 36 this time around,
so
GroupIt would = B3:E36

What code can perform this correctly?

Sub SortGroup()
Dim GroupIt As Range
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'MsgBox LastRow
End If
GroupIt = "B3:E" + LastRow
Range(GroupIt).Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' ,
DataOption1:=xlSortNormal

End Sub

All times are GMT +1. The time now is 01:30 PM.