Create Macro to Add Rows when the # of rows vary each month

[debi0662]

Hi,

I am trying to create a macro that will do some formatting and one of the things I would like to do is for the macro to add up several different columns. The thing is the number of rows will vary from month to month and I would like totals to be just underneath the last row. What's the best way to do this?

I have attached a sample of my document.

Thanks for the help.

[Arkadi]

The easiest way is probably to calculate the last row, then put a sum formula in the columns you want totalled... Assuming column A always has data, something like this:

Sub debi()

Dim ws As Worksheet
Dim lr As Long

Set ws = Worksheets("04-17Test")
lr = ws.Range("A" & Rows.Count).End(xlUp).Row 'finds last row with data in Col A

ws.Range("H" & lr + 1).Formula = "=sum(H2:H" & lr & ")" 'places formula in the first blank row to sum column H2 to the last row with data

End Sub

[debi0662]

Hi Arkadi,

Thanks for the help. Unfortunately, as I am new to macros that means nothing to me. How would I use what you provided to create a macro? How would I make the changes for the columns I want added up?

Debi

[Arkadi]

Hi debi,

What I provided is not so much used to make a macro.... it IS a macro I realize you may want to integrate it into other code, if so maybe provide what you have and I can help.

This line:

ws.Range("H" & lr + 1).Formula = "=sum(H2:H" & lr & ")"

uses the variable "lr" (which is the last row with data in it in column A, to build the forumla for the total of column H, summing the second row to the last one.
if you want to change that column, just change the letter H (3 places) to another column letter, or if you simply want to add other totals, copy the line, and paste it either above or below, and change the H in the copy to match the other column's letter.
Hope that helps a little, but feel free to come back with more questions.

[debi0662]

Hi,

I have attached one of my sample spreadsheets and then my formatting macro as I wasn't sure where to insert it to the other tasks within the macro.

Thanks for the help.

[Arkadi]

Hi debi,

I noticed you have a recorded macro, which is a great tool, but often can use some cleanup. I took the liberty of making some changes, in particular removing tasks where a range or cell is selected, and then the code uses "selection." ....
In those cases it is almost always better to work with the range itself. Have a look and see if this produces the desired result?:

Sub Formatting1()
'
' Formatting1 Macro

Dim lr As Long

Application.ScreenUpdating = False
Range("A1, C1").Select
Cells.EntireColumn.AutoFit
With Rows("1:1")
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Columns("B:C").Delete Shift:=xlToLeft
Range("S1:V1").EntireColumn.Delete
Columns("F:H").NumberFormat = "$#,##0.00"
Columns("P:R").NumberFormat = "$#,##0.00"

lr = Range("A" & Rows.Count).End(xlUp).Row 'finds last row with data in Col A

With Range("" & "F" & lr + 1 & ":H" & lr + 1 & "," & "P" & lr + 1 & ":R" & lr + 1 & "")
    .Formula = "=SUM(R[-" & lr - 1 & "]C:R[-1]C)"
'if there are never borders to start, then you can remove all the "... = xlNone" lines below
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeTop).ColorIndex = 0
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).TintAndShade = 0
    .Borders(xlEdgeTop).Weight = xlThin
End With

Application.ScreenUpdating = False

End Sub

[debi0662]

Thank you so much. That works great. Do you have a suggestion of a site that has detailed explanation on how to learn advanced macros?

Debi

[Arkadi]

Glad to help

This thread is full of suggestions: https://www.excelforum.com/excel-pro...materials.html

By using the recorder you will get a lot of help, and as you practice more, you will see that a lot can just be cleaned up after you have the basic structure.
Google was my best friend for learning... I simply googled what I was trying to do, and read some possible approaches, I wish I'd found this forum earlier on, but it has helped me a lot.

Please remember to mark the thread as solved if your question was resolved? Thanks in advance.