PDA

View Full Version : VBA code to sort a sheet



ashgull80
01-17-2014, 08:27 AM
Hi

I have a user form that i use to add a companys details to a sheet.
when i enter the data i would like to sort the companys so they are in order.
this is the code i have so far but this sorts a static range of cells, but i need it to sort the new entry aswell.

Sheets("Payee").Select
Range("B2").Select
ActiveWorkbook.Worksheets("Payee").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Payee").Sort.SortFields.Add Key:=Range("B2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Payee").Sort
.SetRange Range("B2:C20")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

thanks ash

patel
01-17-2014, 08:39 AM
Sub a()
Worksheets("Payee").Select
LR = Cells(Rows.Count, "B").End(xlUp).Row
Range("B2:C" & LR).Sort key1:=Range("B2"), order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortTextAsNumbers
End Sub

Kenneth Hobs
01-17-2014, 08:42 AM
Usually, the first column requires a data input. As such, I usually find the last row that way. e.g.

Range("B2:C" & Range("B" & Rows.Count).End(xlUp).Row)
Obviously, this would be used for the SetRange.

ashgull80
01-17-2014, 09:58 AM
That worked great thanks so much