Excel vba - getting rid of .activate and.select

Question

My code below is run from ThisWorkbook. It opens up wb_gr (already done), and I'm trying to do a AutoFill in sheet "sh_9_overtid_år" from D3 and down to end of last used cell in row A. Code is working, but I want to get rid of all .activate and .selcet, and run it with sheets hidden. But I haven't got the skills to pull it off... Anyone that can help me with how to modify this bit of code?

wb_gr.Activate
sh_9_overtid_år.Select
Range("D3:D3").Select
lr = Range("A" & Rows.Count).End(xlUp).Row
Selection.AutoFill Destination:=Range("D3:D" & lr), Type:=xlFillDefault

Under "Related" is [How to avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1). — SJR, Apr 24 '18 at 15:05

score 1 · Answer 1 · answered Apr 24 '18 at 15:09

1

You can use With statement to work with hidden sheets. Also, Autofill is not needed here I think.

Option Explicit

Sub test()

Dim lr As Long

    With ThisWorkbook.Worksheets("Sheet1")  '<== change this to your workbook.worksheet e.g. maybe  wb_gr.sh_9_overtid_år

        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("D3:D" & lr).Value = .Range("D3")

    End With

End Sub

answered Apr 24 '18 at 15:09

QHarr

83,427
12
54
101

This way of doing it also worked great. Thanks a lot for helping out. – kit99 Apr 24 '18 at 16:07

score 1 · Accepted Answer · answered Apr 24 '18 at 15:11

1

Assuming sh_9_overtid_år is a fully qualified range (and AutoFill is required due to D3 containing a formula), this should do it:

With sh_9_overtid_år
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("D3:D3").AutoFill Destination:=.Range("D3:D" & lr), Type:=xlFillDefault
End With

answered Apr 24 '18 at 15:11

CLR

11,284
1
11
29

Your suggestion worked great. Thanks a lot for helping out. – kit99 Apr 24 '18 at 16:06
Don't forget to mark one as an answer if you're happy with it - whichever suited your needs better. – CLR Apr 25 '18 at 08:51

Excel vba - getting rid of .activate and.select

2 Answers2