CODEWITHSUNDEEP
vbaexcel
user1712550
user1712550

Reputation: 21

Applying a formula and then autofill the data till last visible cell in the column: VBA

I use this code to apply a length formula and then Autofill till the last visible cell but getting an error

Runtime Error '1004'- Method 'Range' of object_Global' failed

Code

Range("C2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
Selection.AutoFill Destination:=Range("C2:C" & Lastrow).SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Upvotes: 2

Views: 794

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

As always it's better to stay away from Select, ActiveCell and Selection.

Try the code below: 

Dim FitRng As Range, Lastrow As Long

Range("C2").FormulaR1C1 = "=LEN(RC[-1])"

Set FitRng = Range("C2:C" & Lastrow).SpecialCells(xlCellTypeVisible)
FitRng.FillDown

If you don't want to use the FillDown method, you can simply use:

FitRng.FormulaR1C1 = "=LEN(RC[-1])"

Upvotes: 1

nightcrawler23
nightcrawler23

Reputation: 2066

from your code it seems you want the length of cells in COl B. The below code works for me. 

Sub x()
    Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=LEN(RC[-1])"
End Sub

Upvotes: 1

Related Questions