Excel VBA - Need to grab a variable size of rows

Question

New to VBA - Recorded the macro below and every time I run it it always selects 309 rows. I am wanting the rows to be fluid (Example: Could be 400 rows, could be 10 depending on data.

 Columns("H:H").Select
    `Selection.Style = "Comma"
    Range("I2").Select
    Selection.EntireColumn.Insert
    Range("H1").Select
    Selection.Copy
    Range("I1").Select
    ActiveSheet.Paste
    Range("I2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]=""C"",RC[-1]*-1,RC[-1])"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I309")
    Range("I2:I309").Select
    Columns("I:I").Select
    Selection.Copy
    Range("I1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H4").Select
    Application.CutCopyMode = False
    Selection.EntireColumn.Delete
    Range("L7").Select
    Selection.EntireColumn.Insert
    Range("K1").Select
    Selection.Copy
    Range("L1").Select
    ActiveSheet.Paste
    Range("L2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],4)"
    Range("L3").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L309")
    Range("L2:L309").Select
    Columns("L:L").Select
    Selection.Copy
    Range("L1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("K2").Select
    Application.CutCopyMode = False
    Selection.EntireColumn.Delete
    Range("M23").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Range("K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Range("C281").Select
    Selection.End(xlUp).Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Range("C2:D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Range("$C$2:$D$309").RemoveDuplicates Columns:=Array(1, 2), _

You really want to [avoid activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Mar 05 '19 at 22:06
Hi vizzle - please post your entire macro, from `Sub` to `End Sub` - something is missing at the bottom. — dwirony, Mar 05 '19 at 22:09

score 2 · Accepted Answer · answered Mar 05 '19 at 22:14

There's too much going on here for me to translate all of this code for you, but I believe the word you're looking for here is dynamic. In order to replace 309 with a dynamic last row number, you'll need to change:

Selection.AutoFill Destination:=Range("I2:I309")

to

Selection.AutoFill Destination:=Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row)

and

Selection.AutoFill Destination:=Range("L2:L309")

to

Selection.AutoFill Destination:=Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)

etcetera, etcetera. Also like @cybernetic.nomad said, you should read that link he shared with you.

Excel VBA - Need to grab a variable size of rows

1 Answers1