0

I recorded a macro that calculates using SUMIF and then autofills.

Range("H2").Select
ActiveCell.FormulaR1C1 = "=SUMIF(RC[-5], ""10"", RC[-4])"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H3798")
Range("H2:H3798").Select

Is there a way to adjust the code so that it recognizes if the range is bigger or smaller than the "H2:H3798" range I used to make the macro? The data will always start at "H2" but will always have a different end range.

I tried to adjust it so it read "H2:H" & lastRow" but that led to a runtime error.

NiBagMo
  • 3
  • 2
  • [this may be what you're looking for](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – cybernetic.nomad Jun 06 '23 at 16:31
  • 1
    Assuming you're finding the last row based on column A, `Dim lastRow As Long`, `lastRow = Cells(Rows.Count, "A").End(xlUp).Row`, `Range("H2:H" & lastRow).formulaR1C1 = "=SUMIF(RC[-5], ""10"", RC[-4])"` – BigBen Jun 06 '23 at 16:51

0 Answers0