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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,510
Messages
5,832,169
Members
430,114
Latest member
kefier

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