Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem sorting a range of columns using VBA
Hopefully someone can help…
My problem, the sort in VBA seems to sort more columns than specified in the stated range. I have a VBA app that adds data to various columns in a spreadsheet. I use the data in the spreadsheet to populate dropdowns in dialog boxes. The last step in the data maintenance function I to add data to the columns is to sort the column(s) I updated to ensure they display correctly when used in a list box later on. My code is as follows: Workbooks(glbMasterFileName).Worksheets("System Data").Activate LastUsedEmplRow = (Worksheets("System Data").Range("F65536").End(-4162).Row) - 0 'Returns last used row With Worksheets("System Data") .Range("F2:H" & LastUsedEmplRow).Sort Key1:=.Range("F2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Also tried this: With Worksheets("System Data") Range("F2:H" & LastUsedEmplRow).Sort key1:=Range("F1"), Order1:=xlAscending End With I am trying to sort three Colums (F, G & H - Based on values in col F) independent of the other columns in the spreadsheet. The above code sorts the 3 columns I specified ok but also sorts other columns adjacent to the columns I specified in the sort. This cause problems because it breaks the order of the other columns and/or adds blank cells, etc. Can anyone point me in the right direction on this? Thanks, Lakehills |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem sorting a range of columns using VBA
Thanks all but after recording a macro again and tweaking it slightly,
came up with the below code which seems to work perfectly. Workbooks(glbMasterFileName).Worksheets("System Data").Activate LastUsedEmplRow = (Worksheets("System Data").Range("F65536").End(-4162).Row) - 0 'Returns last used row Range("F2:H" & LastUsedEmplRow).Select ActiveWorkbook.Worksheets("System Data").Sort.SortFields.Clear ActiveWorkbook.Worksheets("System Data").Sort.SortFields.Add Key:=Range("F2") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("System Data").Sort .SetRange Range("F3:H" & LastUsedEmplRow) .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Thanks, Lakehills |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting a named range using first three columns | Excel Programming | |||
Problem with sorting by date columns | Excel Discussion (Misc queries) | |||
Problem Sorting Excel Range in VBS | Excel Programming | |||
Problem with Range and Sorting Data | Excel Discussion (Misc queries) | |||
Button to sort a range, and sorting 12 columns | Excel Worksheet Functions |