Referencing formula for row number in Excel Macro

Question

I'm attempting to make a macro which will autofill a number of pages based on how many is actually needed. Each item, imported from another workbook, would receive its own 'page'. These pages are on one sheet, separated by print area. Currently I have a counter which counts the number of items that need pages (11 in my example), then I have a value for number of rows per page (34). Those are multiplied together to determine how many rows to autofill to (374). The issue is getting the macro to replace the range variable with the number (374).

I've tried to use the Indirect function but receive a compile error: "Expected: List separator or )" I've also attempted the Offset function but receive the same error.

This is my Macro so far:

Sub Sheet_Fill()

Sheet_Fill Macro

 ActiveCell.Range("A1:I34").Select

  Selection.AutoFill Destination:=ActiveCell.Range("A1:I68"), Type:= _
        xlFillDefault

ActiveCell.Range("A1:I68").Select

End Sub

Essentially, I want to replace the "I68" with something that will autofill the row number of the I column with my formula mentioned earlier. In the example case of 11 parts, the number would be autofilled with "374"

score 0 · Answer 1 · answered Mar 25 '19 at 16:35

Try:

Selection.AutoFill Destination:=ActiveCell.Range("A1:I" & lr), Type:= xlFillDefault

Where lr is last row... you would either want to determine this dynamically, or you could assign the to pull a value from a cell, e.g.:

'dynamic
lr = sheets(1).cells(sheets(1).rows.count,1).end(xlup).row

'from a cell
lr = sheets(1).range("A1").value

General comment... avoid using select/activate!

score 0 · Accepted Answer · answered Mar 25 '19 at 16:35

The raw version of your macro should be something like this:

Sub Sheet_Fill()

'Sheet_Fill Macro

Dim rownum As Long
rownum = 374 'Or a range? Where is this number located?

 ActiveCell.Range("A1:I34").Select

  Selection.AutoFill Destination:=ActiveCell.Range("A1:I" & rownum), Type:= _
        xlFillDefault

ActiveCell.Range("A1:I" & rownum).Select

End Sub

Cleaned up a bit (this should work):

Sub Sheet_Fill()

    Dim rownum As Long
    rownum = 374 'Or a range? Where is this number located?

    Range("A1:I34").AutoFill Destination:=ActiveCell.Range("A1:I" & rownum), Type:=xlFillDefault

End Sub

Need some more details from you on where you're storing this 374 - is it in a cell? Can it be calculated from your table setup? Until those answers are given, this is the best I can do.

I do have the 374 stored in cell on a different sheet where it's calculated — Travis Benge, Mar 25 '19 at 16:39
Then you can change that 374 to `Worksheets("Sheet1").Range("A1")` (if that was where it was stored, for example... — dwirony, Mar 25 '19 at 16:39

Referencing formula for row number in Excel Macro

2 Answers2