Fill a column with a formula

K

kevcar40

Hi

AM1 holds the vlaue of the last row of data ie 725

i paste this fomula into AT1
=IF(ISERROR(SMALL(AS:AS,ROWS($BH
$1:BH1))),"",INDEX(BH:BH,MATCH(SMALL(AS:AS,ROWS($BH$1:BH1)),AS:AS,0)))

then fill the column upto row 2000 i do this to make sure i dont miss
any rows of data

Selection.AutoFill Destination:=Range("AT1:AT2000"),
Type:=xlFillDefault

is it possible to only fill to the last row off data

thanks


kevin
 
B

Bernie Deitrick

Kevin,

Change the "BH" to the column that determines the size of your data set:

Dim RowCount As Long
RowCount = Cells(Rows.Count, "BH").End(xlUp).Row
Range("AT1").AutoFill Destination:=Range("AT1").Resize(RowCount)

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif formula 1
Fill Blank cells in a range 2
autofill variable lenght column w/ sequential numbers using VBA 2
Concatenate column with variable to set a range 2
Modify range in VBA 9
Fill the column with the last number 6
PIVOT TABLE with a MACRO for FILLing a column with a formula 1
xlFillDefault, based on values in ColA to ColZ 6

Top