Delete used range from every worksheet in workbook

Question

i have this test dataset:

i have it on multiple sheets, but its always different range - more columns, more rows etc. Beneath this "header" are always some blank rows.

I would like to loop throug all sheets and select this header using End(xlDown) and End(xlToRight).

I am trying to do this with the following code:

    Sub WorksheetLoop()

    Dim ws As Worksheet
    Dim rng As Range

    For Each ws In ActiveWorkbook.Worksheets
        Set rng = Range("A1", Range("A1").End(xlDown).End(xlToRight))
            rng.Delete
    Next ws

End Sub

This macro deletes everything on first worksheet and nothing happens on any other sheet.

I tried using ws.rng but then i get an object error.

Can you please tell me what am i doing wrong?

I am studying some VBA material and trying to make changes, but i always end up with an error.

Thanks

Set rng = ws.Range("A1", ws.Range("A1").End(xlDown).End(xlToRight)) or neater is Set rng = ws.Range("A1").currentregion. — SJR
oh wow, why is the solution always so easy :( i was adding prefix in front of first Range not the second. So i always need to put worksheet reference in front of every range in statement. Another note taken, thank you :) — Raymond_90
@Raymond_90 - this is a great use case for a With ws...End With block. — BigBen
You do, not doing so is a common reason for questions here so you are ahead of the curve. — SJR
@BigBen thank you, i will look into it, SJR thank you again, can i somehow flag your comment as solution? — Raymond_90

SJR SJR · Accepted Answer · 2019-10-16T16:34:45

This error is very common.

You must add a sheet reference to every instance of Range (and Cells) as otherwise your range can be straddling two sheets (the specified one and the active sheet, which is implied in the absence of anything else), which causes an error.

Set rng = ws.Range("A1", ws.Range("A1").End(xlDown).End(xlToRight))

In this particular case, you could also consider this variation:

Set rng = ws.Range("A1").currentregion

CurrentRegion.

Delete used range from every worksheet in workbook

1 Answers