How to avoid using select in ranges VBA

Question

I'm running main Sub which works fine. At the end of Main() I'm calling sub to do other thing to clean up code of Main.

Sub DragDown()
    Dim TargetWS As Worksheet
    Set TargetWS = ThisWorkbook.Worksheets("Dane")
    
    Dim LRs As Long
    Dim LastRow As Long
    
    LRs = TargetWS.Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = TargetWS.Cells(Rows.Count, 25).End(xlUp).Row
    
    TargetWS.Range(Cells(LastRow, 25), Cells(LastRow, 30)).Select
    Selection.AutoFill Destination:=TargetWS.Range(Cells(LastRow, 25), Cells(LRs, 30)), Type:=xlFillDefault

End Sub

Both subs are saved in same Module, but DragDown() crash at TargetWS.Range(Cells(LastRow, 25), Cells(LastRow, 30)).Select with error 1004 method range of object _worksheet failed.

So how to fix this? Or how to get rid of .select method?

I tried to use something like Dim rng as Range Set rng = Range(Cells(LastRow, 25), Cells(LastRow, 30)) but it doesn't seem to work...

Edit: SOLVED! by VBasic2008 in comments

    Dim srg As Range
    Set srg = TargetWS.Range(TargetWS.Cells(LastRow, 25), TargetWS.Cells(LastRow, 30))
    Dim drg As Range
    Set drg = TargetWS.Range(TargetWS.Cells(LastRow, 25), TargetWS.Cells(LRs, 30))
    srg.AutoFill Destination:=drg, Type:=xlFillDefault```

Why don't you use variables: `Dim srg As Range` : `Set srg = TargetWS.Range(TargetWS.Cells(LastRow, 25), TargetWS.Cells(LastRow, 30))` : `Dim drg As Range` : `Set drg = TargetWS.Range(TargetWS.Cells(LastRow, 25), TargetWS.Cells(LRs, 30))` : `srg.AutoFill Destination:=drg, Type:=xlFillDefault`. Btw, in the rare cases when you need to `Select` a range (cell), you have to make sure that the right worksheet is active: e.g. `TargetWS.Activate` : `srg.Select` : `Range("A1").Select`. You could benefit from reading [`How to avoid using Select in Excel VBA`](https://stackoverflow.com/q/10714251). — VBasic2008, Feb 16 '21 at 16:31
Thank you @VBasics2008 this works like a charm! I've read linked question but now I understand my mistake :) — Patrycjusz, Feb 17 '21 at 08:57

How to avoid using select in ranges VBA

0 Answers0