How to change macro to use different column..

johnz1970

New Member
Joined
Mar 28, 2011
Messages
1
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
Everywhere you see

Code:
Cells(myRow, 1)

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

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,965
Members
414,114
Latest member
Lost_User21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top