 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Sort Columns using lastrow
Hi all, this is a snippet of VBA code that I recorded and added some "lastrow" information into (line numbers added for the sake of my question): 1 Dim lastrow As Integer 2 lastrow = Cells(Rows.Count, 1).End(xlUp).Row 3 Worksheets("Working Folder").Range("A1:" & "S" & lastrow).Select 4 ActiveWorkbook.Worksheets("Working Folder").Sort.SortFields.Clear 5 ActiveWorkbook.Worksheets("Working Folder").Sort.SortFields.Add Key:=Range( _ ""N:" & "N" & lastrow"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal 6 With ActiveWorkbook.Worksheets("Working Folder").Sort 7 .SetRange Range("A1:" & "S" & lastrow) 8 .Header = xlYes 9 .MatchCase = False 10 .Orientation = xlTopToBottom 11 .SortMethod = xlPinYin 12 .Apply 13 End With I'm getting the compiler error: expected: list separator or ")". It is failing in the section: ""N:" & "N" & lastrow What I'm trying to accomplish is select A1 down to S(lastrow) and sort by column N1:N(Lastrow). Hopefully I'm just missing a parenthesis or another set of quotes? Thanks for assistance. For starters, change 2 to: lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row > Hi all, this is a snippet of VBA code that I recorded and added some > "lastrow" information into (line numbers added for the sake of my [quoted text clipped - 25 lines] > by column N1:N(Lastrow). Hopefully I'm just missing a parenthesis or > another set of quotes? Thanks for assistance. For starters, change 2 to: lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row > Hi all, this is a snippet of VBA code that I recorded and added some > "lastrow" information into (line numbers added for the sake of my [quoted text clipped - 25 lines] > by column N1:N(Lastrow). Hopefully I'm just missing a parenthesis or > another set of quotes? Thanks for assistance. Key:=Range( _ ""N:" & "N" & lastrow"), SortOn:=xlSortOnValues, Change to: Key:=Range("N1"), SortOn:=xlSortOnValues, Sort Key requires only one cell (range) designation. > Hi all, this is a snippet of VBA code that I recorded and added some > "lastrow" information into (line numbers added for the sake of my [quoted text clipped - 25 lines] > by column N1:N(Lastrow). Hopefully I'm just missing a parenthesis or > another set of quotes? Thanks for assistance. Also, this: .SetRange Range("A1:" & "S" & lastrow) Would work as: .SetRange Range("A1:S" & lastrow) > Hi all, this is a snippet of VBA code that I recorded and added some > "lastrow" information into (line numbers added for the sake of my [quoted text clipped - 25 lines] > by column N1:N(Lastrow). Hopefully I'm just missing a parenthesis or > another set of quotes? Thanks for assistance. Also, this: .SetRange Range("A1:" & "S" & lastrow) doesn't make sense as it stands. What is SetRange? Is it a public variable, macro? If you are trying to set a range, then use: .Set myRange = Range("A1:S" & lastrow) > Hi all, this is a snippet of VBA code that I recorded and added some > "lastrow" information into (line numbers added for the sake of my [quoted text clipped - 25 lines] > by column N1:N(Lastrow). Hopefully I'm just missing a parenthesis or > another set of quotes? Thanks for assistance. This is the format and syntax that I use. Sub SortJohnny() Dim lastRow As Long lastRow = Sheets("Working Folder").Cells(Rows.Count) _ .End(xlUp).Row Set Wks = Sheets("Working Folder").Range("A1:S" & lastRow) wks.Sort Key:=Range("N1"), Header:=xlYes, MatchCase:=False, _ Orientation:=xlTopToBottom, SortMethod:=xlPinYin End Sub > Hi all, this is a snippet of VBA code that I recorded and added some > "lastrow" information into (line numbers added for the sake of my [quoted text clipped - 25 lines] > by column N1:N(Lastrow). Hopefully I'm just missing a parenthesis or > another set of quotes? Thanks for assistance. I wouldn't use wks to represent a range <vbg>. And I'd qualify that key1:=... stuff. And I thought that xlpinyin was for Chinese characters! Sub SortJohnny() Dim lastRow As Long dim rng as range lastRow = Sheets("Working Folder").Cells(Rows.Count).End(xlUp).Row Set rng = Sheets("Working Folder").Range("A1:S" & lastRow) rng.Sort Key1:=sheets("working folder").Range("N1"), Header:=xlYes, _ MatchCase:=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin End Sub > This is the format and syntax that I use. > [quoted text clipped - 36 lines] > > by column N1:N(Lastrow). Hopefully I'm just missing a parenthesis or > > another set of quotes? Thanks for assistance.  SignatureDave Peterson Hi Dave, Haste makes waste! You're right about the PinYin and according to VBA help, that is the default method. I think the OP tuned out anyhow. > I wouldn't use wks to represent a range <vbg>. And I'd qualify that key1:=... > stuff. [quoted text clipped - 50 lines] > > > by column N1:N(Lastrow). Hopefully I'm just missing a parenthesis or > > > another set of quotes? Thanks for assistance. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.