| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort 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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Columns using lastrow
For starters, change 2 to:
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row "Johnny" wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Columns using lastrow
For starters, change 2 to:
lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row "Johnny" wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Columns using lastrow
Key:=Range( _
""N:" & "N" & lastrow"), SortOn:=xlSortOnValues, Change to: Key:=Range("N1"), SortOn:=xlSortOnValues, Sort Key requires only one cell (range) designation. "Johnny" wrote: 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Columns using lastrow
Also, this:
..SetRange Range("A1:" & "S" & lastrow) Would work as: ..SetRange Range("A1:S" & lastrow) "Johnny" wrote: 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Columns using lastrow
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) "Johnny" wrote: 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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Columns using lastrow
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 "Johnny" wrote: 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Columns using lastrow
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 JLGWhiz wrote: 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 "Johnny" wrote: 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. -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Columns using lastrow
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. "Dave Peterson" wrote: 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 JLGWhiz wrote: 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 "Johnny" wrote: 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. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sort by color: Is there an easy way to sort columns or rows in EX | Excel Worksheet Functions | |||
| button to seach columns for blank cells, then sort by two columns | Excel Programming | |||
| sort by two columns ends up with wrong sort in 2nd col | Excel Discussion (Misc queries) | |||
| data sort is not including all columns in sort | Excel Discussion (Misc queries) | |||
| Go to lastrow using other column's lastrow | Excel Programming | |||
![Johnny[_11_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
