Problem sorting a range of columns using VBA

  • Thread starter Thread starter Lakehills
  • Start date Start date
L

Lakehills

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
 
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
 
Back
Top