How to change macro to use different column..

[johnz1970]

I'm new to Excel macros/VBA and stumbled on this website for help. I found an example of a macro on this site that allows me to do almost what I wanted for my spreadsheet except it picks from Column A. How to I modify it to insert lines choosing from Column D for example?

If beggers can be choosers, can a automatic sort option be added to Column D to sort from low to high before this macro starts too?

Sub SplitListAndSubTotal()
'
' SplitListAndSubTotal Macro
' Macro recorded 19/03/2004 by GaryB
'
Dim myRow As Long
Dim MyStart As Long
MyStart = 2
myRow = 3 'or use 2 if you haven't got a header
Do Until Cells(myRow, 1) = ""
If Cells(myRow, 1) = Cells(myRow - 1, 1) Then
myRow = myRow + 1
Else
Cells(myRow, 1).EntireRow.Insert
Cells(myRow, 3) = Application.WorksheetFunction.Sum(Range(Cells(MyStart, 3), Cells(myRow - 1, 3)))
Cells(myRow + 1, 1).EntireRow.Insert
myRow = myRow + 3
MyStart = myRow - 1
End If
Loop
Cells(myRow, 3) = Application.WorksheetFunction.Sum(Range(Cells(MyStart, 3), Cells(myRow - 1, 3)))

'
End Sub

 

[craig.penny]

Everywhere you see

Cells(myRow, 1)

or anything similar, it refers to (row,column). If you want column D you should replace 1 with 4 like this

Cells(myRow, 4)

As far as sorting goes, click on "Record Macro" and do your sort. Click "Stop Recording" then look at the code to see how it's done. Try to modify the code to suit your needs. Post back if you have troubles.