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 |
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 |
All times are GMT +1. The time now is 03:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com