-2

I'm working with a Macro that generates a column that is the sum of the other two columns in a table. However, the table's row number is not always the same, so when I run the Marco for a table that has less rows, some extra cell in that column will give 0, is there a way I can get rid of them?

The Marco code is here:

 Sub sum()
    '
    ' sum Macro
    '
    ' Keyboard Shortcut: Ctrl+s[![enter image description here][1]][1]
    '
        Range("AV1").Select
        ActiveCell.FormulaR1C1 = "AV"
        Range("AV2").Select
        ActiveCell.FormulaR1C1 = "=RC[-6]+RC[-4]"
        Range("AV2").Select
        Selection.AutoFill Destination:=Range("AV2:AV84")
        Range("AV2:AV84").Select
        ActiveWindow.SmallScroll Down:=-30
        Range("AV1").Select
    End Sub

The result is here: enter image description here

Please help thank you.

braX
  • 11,506
  • 5
  • 20
  • 33
Kenny smith
  • 27
  • 4
  • You have '84' FIXED in you macro, why do you think there are 'extra cells' are created? And if you think 3 seconds longer, what will be the solution ? – Luuk Aug 10 '20 at 19:18

1 Answers1

4

Without all the Select...

Sub sum()
    Dim  lr as long
    With Activesheet
        .Range("AV1").Value = "AV"
        lr = .Cells(.rows.count, "AR").End(xlup).Row 'last occupied row in Col AR
        .Range("AV2:AV" & lr).FormulaR1C1 = "=RC[-6]+RC[-4]"
    End with
End Sub

Useful to review this: How to avoid using Select in Excel VBA

Tim Williams
  • 154,628
  • 8
  • 97
  • 125