Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > vba sort range problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,092
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help! I am having a problem with sort! RobertD[_2_] Excel Discussion (Misc queries) 2 August 29th 08 07:56 PM
formula to sort a range so that it matches the exact rows of a column that is outside that range? steveo Excel Discussion (Misc queries) 1 June 18th 06 02:05 AM
Sort range changes during sort coffedrinker2003 Excel Discussion (Misc queries) 1 May 24th 05 11:53 PM
Sort Problem [email protected] Excel Programming 6 May 13th 05 06:33 PM
sort problem derekc[_22_] Excel Programming 2 August 5th 04 07:34 PM


All times are GMT +1. The time now is 04:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement