How can I make a macro calculate formulas for a variable number of rows?

[MacroNoob1]

Hi everybody,

This is my first post, so a big thanks to anyone who will help!

Here's the thing...

I routinely export a dataset into Excel that has a different number of rows every time. I would like to make a macro that calculates several formulas at the bottom for each column. For example:

ID| Price | Sq Ft | Etc.
1 | $250 | 2,000 |
2 | $200 | 1,800 |
3 | $185 | 1,750 |
Etc.
Etc.
Etc.

Min | $185 | 1,750
Avg | $211 | 1,850
Max | $250 | 2,000

I recorded a macro that works with the current dataset, but it doesn't work correctly if the number of rows changes. I know how to open and use the script editor, but I don't know VBA, so my troubleshooting skills are pretty limited. Can anyone help me?

Thank you,

MacroNoob1

 

[igold]

Hi MacroNoob1,

Welcome to the MrExcel Forum.

See if this does what you want...

Sub test()


    Dim lRow As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    Cells(lRow + 2, 1).Value = "Min"
    Cells(lRow + 3, 1).Value = "Avg"
    Cells(lRow + 4, 1).Value = "Max"
    Cells(lRow + 2, 2).Formula = "=min(B2:B" & lRow & ")"
    Cells(lRow + 3, 2).Formula = "=Average(B2:B" & lRow & ")"
    Cells(lRow + 4, 2).Formula = "=max(B2:B" & lRow & ")"
    Cells(lRow + 2, 3).Formula = "=min(C2:C" & lRow & ")"
    Cells(lRow + 3, 3).Formula = "=Average(C2:C" & lRow & ")"
    Cells(lRow + 4, 3).Formula = "=max(C2:C" & lRow & ")"


End Sub

HTH

 

[MacroNoob1]

igold,

Thanks, it works! This forums is awesome!

Two follow-up questions:

1) Can we make it do this for a bunch of columns? I wasn't very clear, but I actually have 47 columns of data.

2) I'd also like to reformat the 3 rows on the bottom, because some are dollar amounts, some are numbers, some are percentages, etc. Is there a way to do this, since the row numbers will vary, depending on the dataset?

Any additional help would be really appreciated. If I need to learn a little VBA to understand what you're doing well enough to implement it, that's OK. I've been searching for tutorials and it looks really interesting.

Thanks a ton!

MacroNoob1

 

[igold]

Hi MacroNoob1,

I made some changes that should meet your additional requirements. I also took the time to write comments to the code so you know what each section of code is doing. The formats for the "Calculated Totals" are being copied from first row of data (Row 2). Whatever the format is in any column in row 2 is being copied down the entire column.

Sub test()


    Dim lRow As Long, lCol As Long, i As Long, x As Long
    Dim hdr
    Dim rng As Range
    
    'Find last row and last column with data
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    'Set range rng equal to row 2 - all formats will be pulled from this row
    Set rng = Range("B2", Cells(2, lCol))
    
    'Set the three vertical totals names in column 1
    hdr = Array("Min", "Avg", "Max")
    Range(Cells(lRow + 2, 1), Cells(lRow + 4, 1)) = Application.Transpose(hdr)
    
    'Write the formula for "Min" to the first column and then drag it to right
    Cells(lRow + 2, 2).Formula = "=min(B2:B" & lRow & ")"
    Range(Cells(lRow + 2, 2), Cells(lRow + 2, lCol)).FillRight
    
    'Write the formula for "Average" to the first column and then drag it to right
    Cells(lRow + 3, 2).Formula = "=Average(B2:B" & lRow & ")"
    Range(Cells(lRow + 3, 2), Cells(lRow + 3, lCol)).FillRight
    
    'Write the formula for "Max" to the first column and then drag it to right
    Cells(lRow + 4, 2).Formula = "=max(B2:B" & lRow & ")"
    Range(Cells(lRow + 4, 2), Cells(lRow + 4, lCol)).FillRight
    
    'Copy the format in the first cell of each column (Row 2) and apply to entire column
    For x = 2 To lCol
        Columns(x).NumberFormat = rng(x - 1).NumberFormat
    Next
    
End Sub

I hope this helps you some...

 

[MacroNoob1]

igold, you're a saint. This is perfect, and the explanations are really helpful. Thanks!!!

 

[igold]

I am glad I helped you some, I was happy to do it. Thanks for the feedback.