Excel Macro Question (simple I'm sure)

[JCO]

I have several Macro's that are written for excel, but I don't know how to
get all of them to automatically run together as a single program. Can
someone tell me how that is done.

Currently each Macro is in a VB Style Sub Routine.
Example:
Sub DeleteTopRows
...
...code
...
End Sub

I have 8-10 of these sub routines that I want to string together.
Thanks

 

[L. Howard Kittle]

Sub DeleteTopRows
...
...code
...
"enter the name of the next macro you want to run here"
End Sub

Do the same with all the others.

HTH
Regards,
Howard

 

[L. Howard Kittle]

Maybe I should add:

Now run the first macro and the others will be called as each macro runs.

Howard

 

[JCO]

I got it. That was easy. Thanks
Here's the next task to be done.. maybe you can help.

At this point, I have an Excel sheet that contains 3-columns (Assistance,
Amount, Units). This is already sorted by column "A" which is Assistance.
How do I traverse down column "A" and determine when the this Category
changes? I need to Insert a couple of Rows then subtotal column "B".

I know this task is a bit more complicated. If you (or anyone else) can
help, that would be great.

Thanks

 

[Gord Dibben]

This will insert two rows at every change in category in column A.

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To 3 Step -1
If Cells(X, 1).Value <> Cells(X - 1, 1).Value Then
If Cells(X, 1).Value <> "" Then
If Cells(X - 1, 1).Value <> "" Then
Cells(X, 1).Resize(2, 1).entirerow.Insert
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[L. Howard Kittle]

I suggest you post the next step of your project in a new thread with the
details of how you want it to work, sorta like you did here.

I played with a possible solution and could only identify where a change in
column A takes place but cannot figure how to get two rows inserted and then
a subtotal formula in column C.

If I make a break-through on it I will post it here.

Regards,
Howard

 

[JCO]

Yes it does. That works great.
Now to figure out totals?
Thanks.

 

[Gord Dibben]

If all you want is a subtotal of column B, you don't need the inserted rows.

Simply use Data>Subtotal

For each change in column A, add a subtotal for column B.

If you want a blank row, you could use a macro to insert a row above or
below the word Total in column A


Gord

 

[Steve]

Hi
If you add a few lines to the code below so that you now have

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
Dim Y As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
Y = LastRow + 1
For X = LastRow To 3 Step -1
If Cells(X, 1).Value <> Cells(X - 1, 1).Value Then
If Cells(X, 1).Value <> "" Then
If Cells(X - 1, 1).Value <> "" Then
For t = X To Y - 1
Cells(Y, 2).Value = Cells(Y, 2).Value + Cells(t, 2).Value
Next t
Y = X
Cells(X, 1).Resize(2, 1).EntireRow.Insert
End If
End If
End If
Next X
For t = X To Y - 1
Cells(Y, 2).Value = Cells(Y, 2).Value + Cells(t, 2).Value
Next t
Application.ScreenUpdating = True
End Sub

Then this should add a subtotal to each group in column B


Regards
Steve

 

[Gord Dibben]

Ignore this.

Steve's revised macro looks good.


Gord

 

[Steve]

No Ignore Steve
Why make life difficult when it's so easy. I didn't know that Subtottal
existed. I guess you are never to old to learn.

Regards
Steve

 

[L. Howard Kittle]

Hi Steve, just curious.

If you were to Dim t in the code addition you supplied, what would it be?

If I do not block out Option Explicit I get variable not defined for t.

I tried Integer, Long, Double, Variant... no workie.

Regards,
Howard

 

[Steve]

Hi Howard,
I know it's bad practice on my part. I am forever forgetting to declare
variables, luckily most basic based programming languages are quite
forgiving. "t" only keeps the count of the items within the present group and
as such should only be a small number. In the test data that I used declaring
the variable as Byte works fine but Interger/Long/Double/Variant should also
work.

Regards
Steve