Autofill script question

[Gor_yee]

Range("L6").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-3]=RC[-2],""Unreviewed"",""Reviewed"")"
Range("L6").Select
Selection.AutoFill Destination:=Range("L6:L483")
Range("L6:L483").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="=""Unreviewed"""


I've got this formula...what i need to know is how do I get the script
to autofill to the last cell??As it will be different for each sheet,
as not all range will end up at L483, i have some that will end at
L700, L658. So how do i make the script always go to the last cell??
Please help

 

[Guest]

hi,
there is more that one way to do this. here's one
Sub copytest()
Range("L6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=RC[-2],""Unreviewed"",""Reviewed"")"
ActiveCell.Copy
Range(Range("L6"), Range("L6").Offset(0, -1) _
.End(xlDown).Offset(0, 1)).Select
ActiveSheet.Paste
'rest of your code here?
End Sub
this assumes that the K column is a solid block of data. if not then come up
from the bottom.
Sub copytest()
Range("L6").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=RC[-2],""Unreviewed"",""Reviewed"")"
ActiveCell.Copy
Range("k10000").End(xlUp).Offset(0, 1).Select
Range(ActiveCell, ActiveCell.End(xlUp)).Select
ActiveSheet.Paste
'rest of your code here?
End Sub

and there are other ways too. this is just 2

Regards
FSt1

 

[Gord Dibben]

Example code.........

Do you have a column to check for last cell?

I used adjacent column K in the example.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Range("L6").Formula = "=B1+C1"
Lrow = Range("K" & Rows.Count).End(xlUp).Row
Range("L6:L" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP