help on sort

[SMILE]

Hello
I have a sheet contains data in colum A to G and I keep on adding dat
below it. I want to run a macro to sort the data. I need a macro t
find the last row and do the sorting on column C. Hope you got what
need.
Thanks
Tom

 

[Dave Peterson]

If there's no other data on your worksheet, you could just sort A:G.

with worksheets("sheet1").range("a:g")
.sort key1:=.columns(3), order1:=xlascending, header:=xlno
end with

if you can use a column to determine the last used row (I used A in my example):

With Worksheets("sheet1")
With .Range("a1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row)
.Sort key1:=.Columns(3), order1:=xlAscending, header:=xlNo
End With
End With

 

[SMILE]

Hi
Let me make it clear my requirement. For example I have some data i
the first 6 rows which includes heading etc. My real data is startin
from row 7 and keep on adding. What i need to run a macro to do
subtotal from row 7(row 7 is the header) till the last row( the las
row number varies). In my data I have only 5 colums(A,B,C,D & E) and
need the subtotals(SUM) in B and E if there is a change in column A. M
problem is to identify the last row. Hope it is clear
Thanks in advance
Tom

 

[Dave Peterson]

You changed from A:G and sorting on column C to A:E and doing a subtotal based
on A?

After you've used the other stuff to sort by column A (I would guess???), then
you could use something like this to subtotal:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim LastRowInCol As Long
Dim iCol As Long
Dim myRng As Range

With Worksheets("sheet1")
FirstRow = 6 'includes last header row
LastRow = 7 'just start with something a little bigger

For iCol = 1 To 5 'A:E
LastRowInCol = .Cells(.Rows.Count, iCol).End(xlUp).Row
If LastRowInCol > LastRow Then
LastRow = LastRowInCol
End If
Next iCol

Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "E"))

'stop any "can't determine header warning"
Application.DisplayAlerts = False
myRng.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Application.DisplayAlerts = True

End With

End Sub

The .subtotal stuff came from recording a macro when I did it manually.