Marco Editing Help

[Gaffnr]

Hi, Very Simple. I dont know macro programming and use the record
functionality.

I used the record facility to create the following.
I copied formulas in cells j2 - V2 down to row 14.
This created the macro below i see when i do the 'step into' button.

Sub Test()
'
' Test Macro
' Macro recorded 05/02/2008 by gaffneyr
'

'
Range("J2:V2").Select
Selection.AutoFill Destination:=Range("J2:V14")
Range("J2:V14").Select
End Sub

Every time I run it, it goes to row 14 only. I want it to run to the last
row of data whatever row the data goes down to, be it 14 or 33,213 etc.
Can anybody edit the above to help me?
Thanks so much
rob

 

[Roger Govier]

Hi

Try
Sub Test()
'
' Test Macro
' Macro recorded 05/02/2008 by gaffneyr
'
Dim lastrow as long
lastrow = ws.Cells(Rows.Count, "J").End(xlUp).Row
Range("J2:V2").Select
Selection.AutoFill Destination:=Range("J2:V" & lastrow)
End Sub

I don't think you needed that last Select line.
If you do, then amend it to
Range("J2:V" & lastrow).Select

 

[Don Guillett]

two lines only. If col J does not have the row desired change col on first
line

lr=cells(rows.count,"J").end(xlup).row
Range("J2:V2").AutoFill Destination:=Range("J2:V" & lr)

 

[Gaffnr]

othanks but i get an error message -
run time error 424 object required
debuuger highlights this line

lastrow = ws.Cells(Rows.Count, "J").End(xlUp).Row

 

[Roger Govier]

Hi Don

Amazing how "blind" I could be to those 2 Selects<bg>

 

[Gaffnr]

Now i get error message 438 obejct does not support this property or method

here is my full macro

Sub Test()
'
' Test Macro
' Macro recorded 05/02/2008 by gaffneyr
'

'
Cells(Rows.Count, "J").End(xlUp).Row
Range("J2:V2").AutoFill Destination:=Range("J2:V" & lr)


End Sub

 

[Don Guillett]

When all else fails, follow instructions

lr= Cells(Rows.Count, "J").End(xlUp).Row
Range("J2:V2").AutoFill Destination:=Range("J2:V" & lr)

 

[Roger Govier]

Sorry

I shouldn't have included ws in front of cells, unless I had set some
Worksheet to be ws first.
It should have read
lastrow = Cells(Rows.Count, "J").End(xlUp).Row


--
Regards
Roger Govier

othanks but i get an error message -
run time error 424 object required
debuuger highlights this line

lastrow = ws.Cells(Rows.Count, "J").End(xlUp).Row

 

[Gaffnr]

my oh my - stupid me. working perfectly now ive copied your full macro
tkls
rob

 

[Don Guillett]

We're glad you got it to work

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

my oh my - stupid me. working perfectly now ive copied your full macro
tkls
rob