Can't autofill a formula to a variable last

Closed
j - Apr 21, 2010 at 02:04 PM
rizvisa1 Posts 4478 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!

Related:

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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 ?
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.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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