Can't autofill a formula to a variable last

Closed
j - Apr 21, 2010 at 02:04 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 14, 2010 at 07:15 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 21, 2010 at 02:54 PM
By blank rows are you saying that there is data and then some blank rows and then again data etc ?
0
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 14, 2010 at 07:15 PM
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
0