Active X (Macro) to Sort data range to included added rows

Hi,

I'm fairly new to macros and the like in excel so bare with me.
I have a sheet which I am using to track spending and have a macro/Active X button to sort the data at the end. The trouble is that as I have added new rows they are not included in the range and have to change that each time. Is there a way to get the macro to check the range by looking for the cells that have data in a specific column, say the date column?

Basically the macro selects the rows and columns with data in and then sorts it by the date range descending.

Heres the macro code

Private Sub CommandButton1_Click()
'
' Kitpartsspending Macro
'

'
    Range("B3:F33").Select
    ActiveWorkbook.Worksheets("Kitparts Spending").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Kitparts Spending").Sort.SortFields.Add Key:=Range _
        ("B3:B33"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Kitparts Spending").Sort
        .SetRange Range("B2:F33")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=15
    Range("E35").Select

End Sub

Any help would be greatly appreciated

Thanks in advance

Try

Private Sub CommandButton1_Click()
'
' Kitpartsspending Macro
'

'
Dim LR As Long
LR = Worksheets("Kitparts Spending").Range("B" & Rows.Count).End(xlUp).Row
    Range("B3:F" & LR).Select
    ActiveWorkbook.Worksheets("Kitparts Spending").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Kitparts Spending").Sort.SortFields.Add Key:=Range _
        ("B3:B" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Kitparts Spending").Sort
        .SetRange Range("B2:F" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Fantastic,
That works a treat thank you...I just need to read over it now and see what it is you've done to make it work

Is there a way to make it select the cell just above the total after sorting too, I know how to do this if I record as a macro but the cell selected again obviously changes when new rows are added.

Thanks for your help

Originally Posted by shg

Another way:

Private Sub CommandButton1_Click()
    Dim wks         As Worksheet
    Dim iRow        As Long

    Set wks = Worksheets("Kitparts Spending")

    With wks
        iRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("B3", .Cells(iRow, "F")).Sort _
                Key1:=.Range("B3"), Order1:=xlAscending, _
                Header:=xlYes, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom
        Application.Goto .Cells(iRow - 1, "B")
    End With
End Sub

Err, for some reason when I try this version and put in a date before the row at the very top it inserts it after the top line. Any idea why that is, otherwise it seems to work?

Originally Posted by tims31

Err, for some reason when I try this version and put in a date before the row at the very top it inserts it after the top line. Any idea why that is, otherwise it seems to work?

Ok, I changed the range selection to B2 and it works ok now..Thanks