Can't autofill a formula to a variable last

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi,

I'm new to excel macros and I'm trying to create a macro that will autofill a formula down to the last row with a value. The number of rows in this spreadsheet will consistly change, so I need the macro to accommodate the number of rows within the document without populating blank rows.

I've reviewed many forums and tried many things...such as

Selection.AutoFill Destination:=Range("C2:C337"), Type:=xlFillSeries

This still populates to C337 even if I delete rows. I need to autofill to however many rows are there depending on the spreadsheet.

I also tried...

Range("E2", Range("E2").End(xlDown)).Name = "typecolrange2"
Selection.AutoFill Destination:=typecolrange2, Type:=xlFillSeries

I'm still getting errors with the range not being correct.

Any help would be appreciated.

THank you!

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
By blank rows are you saying that there is data and then some blank rows and then again data etc ?
I am having the same problem (I think). It seems like the case is that their macro might expand a table or something. Then, they just want the macro to fill series to complete the table. Not extend past the last data cell of the table.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Just find the last row of the sheet. There are many ways.

Most simple is if you have a column that will always have a value you can use

LastRow = cells(rows.count, "A").end(xlup).row

this is based on column A