Deleting rows in Excel VBA without Activating workbook

Question

I have bumped in a little problem. I want to Delete some rows with VBA, but i want to avoid using Activate or select statements. The problem is, that if i don't activate the workbook, my code returns an error when deleting or copying rows.

With Template.Worksheets("1").Range("A3").CurrentRegion
 rCountS = .Rows(.Rows.Count).Row
 .Range("A4", Cells(rCountS, 1)).EntireRow.Delete
End With

The .Range part returns an error below:

Run-time error '1004': Application-defined or object-defined error

Is there a way to avoid using activate when deleting rows in Excel?

Thank you

Not Cells(rCount... but .Cells(rCount... (dot was skipped) — JohnSUN
Thank you for the answer. Did not know it was that simple. :) I would have never figured this one one — Pavel Virant

Havard Kleven Havard Kleven · Accepted Answer · 2020-10-01T13:23:18

There is missing a "." in your code. For an easier reading and adaptability, I would assign the worksheet to a variable as well:

Dim ws as worksheet
Set ws = Template.worksheets("1")

With ws.Range("A3").CurrentRegion
    rCountS = .Rows(.Rows.Count).Row
    .Range("A4", .Cells(rCountS, 1)).EntireRow.Delete
End With

Deleting rows in Excel VBA without Activating workbook

1 Answers