VBA/Macro help (Coding)

[MikeyZ]

Here's my code:

Sub SetInvFormats()
'
' SetInvFormats Macro
'
'
Application.Goto Reference:="R1C3"
Selection.Copy
Application.Goto Reference:="R10C3"
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=ActiveCell.Range("A1:A90000")
ActiveCell.Range("C10:C90000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="R9C3"
ActiveSheet.Range("$A$9:$O$90000").AutoFilter Field:=3, Criteria1:="#N/A"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.Goto Reference:="R9C3"
ActiveSheet.Range("$A$9:$O$90000").AutoFilter Field:=3
ActiveCell.Offset(-2, 0).Range("A1").Select
End Sub


I only want to copy down the formula to the last empty space in column "C".
The last line number varies from day to day.
But when I set up my code I don't want to specify the range by numbers.
ie: $A$9:$O90000

How can I get around that.

Hope this makes sense.

 

[MikeyZ]

I replaced
Selection.AutoFill Destination:=ActiveCell.Range("A1:A90000")
with
Selection.AutoFill Destination:=ActiveCell.Range("C10:C" & lastrow)
But that dumped on me.

 

[James006]

Hi,

You could test the following macro

Sub Macro1()
' Copies from Cell C1 down to the last cell in Column C
Dim lastr As Long
lastr = Cells(Application.Rows.Count, "C").End(xlUp).Row
ActiveSheet.Range("C1").Copy Destination:=Range("C2:C" & lastr)
End Sub

HTH

 

[MikeyZ]

Thanks, I will try it in a few minutes.
Trying to work thru another headache.