USING AUTOFILL IN A MACRO

[Sheron Donaldson]

Hi

Can you help me?

I need to create a calculation and then autofill this
down, in a report, in Excel that I am working on. As I
need to do this weekly I could do with doing this in a
macro, which I have tested. But in the macro it is
looking at row 130 say, and it is not changing if there
are less or more rows. Is there a function like last cell
that I can use? I'm not sure but I could do with some
guidance in what the function is and how to put it into my
macro.

Cheers
Sheron

 

[Don Guillett]

As always, you should post your code. If you want to find the last row go
fromt the bottom up.

lastrow=cells(rows.count,"a").end(xlup).row
then use this in your code
range("a1:a" & lastrow)

 

[Gord Dibben]

Sheron

Sub Auto_Fill()
Dim lrow As Long
With ActiveSheet
lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & lrow).FillDown
End With
End Sub

Fills whatever is in A1 down to bottom of used range in column A.

Gord Dibben Excel MVP

 

[RichardO]

Hi:

I am new to vba, so please bear with me.

I have the following code in my macro:

Range("J2:N2").Select
Selection.AutoFill Destination:=Range("J2:N23")

I think the first row of the code is copying the formula in row 2 o
columns J to N. I think the 2nd row of the code is autofilling th
cells from 2nd row to row 23.

My problem is: what if I have more rows than 23 rows, how can I chang
this code to fill the formula in row 2 to the end of the row
available.

Thanks very much for helping.


Richard

 

[Gord Dibben]

Richard

Sub Auto_Fill()
Dim lrow As Long
With ActiveSheet
lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("J2:N" & lrow).FillDown
End With
End Sub

Assuming column A contains data and you want to fill J2:Nx as far down as
Column A extends.

You could replace the A with another column if that would be more appropriate.

Gord Dibben Excel MVP

 

[RichardO]

thanks, that worked.

Richard