Sub Sort_no() 'sort by No of Column A
Dim FR As Long 'First row
Dim NR As Long ' next row
Dim lastRow As Long
Dim Rng As Range
Dim SortCol As String
Dim fCell As Range
'Where is the beginning of your table located at?
Set fCell = Range("A9")
'Which column are we sorting on?
SortCol = "A"
'Don't put anythine in col A below your sort data
With ActiveSheet
lastRow = .Cells(.Rows.Count, SortCol).End(xlUp).Row
End With
'If you will have other stuff in col A, will need to
'figure out a new way to deifne the end of the table
Do
'Define the boundaries of the section in table
FR = fCell.End(xlDown).Row
NR = Cells(FR, SortCol).End(xlDown).Row
Set Rng = Range(Cells(FR, SortCol), Cells(NR, SortCol)).EntireRow
'Begine sort
With Rng
.Sort key1:=.Cells(1), order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Set fCell = Cells(NR, SortCol)
Loop Until fCell.Row >= lastRow
Range("A9").Select
End Sub