Macro to delete all rows

[_eNVy_]

I have a macro that will identify whether there is any value in a specific cell in each tab. If it is blank, go to next tab but if there is data, delete all data and go to the next.
The issue I am facing is that the code below will delete only the data set but leaves blank rows (checked by going to a tab and pressing Ctrl and End).
I tried to use a range such as

Range("A2:AQ1048576")

but this deleted my headers too.

    Dim ws, ws1c, ws2c, ws3c        As Worksheet
    Dim lRow                                 As Long
    Dim rng                                   As Range
    

    Set ws = Worksheets("Macro Centre")
    Set ws2c = Worksheets("1c client services")
    ws2c.Activate
    
            lRow = Cells.Find(What:="*", _
           After:=Range("A1"), _
           LookAt:=xlPart, _
           LookIn:=xlFormulas, _
           SearchOrder:=xlByRows, _
           SearchDirection:=xlPrevious, _
           MatchCase:=False).Row
    
    Set rng = Range("A2:AQ" & lRow)
    If Not IsEmpty(ws2c.Cells(2, 1).Value) Then
        rng.EntireRow.Delete
    
    End If

Thank you in advance,

 

[Joe4]

That is because if it is blank, the last row calculation is returning 1.
So try amending the end of your code that is doing the deletion like this:

    If lr > 1 Then
        Set rng = Range("A2:AQ" & lRow)
        If Not IsEmpty(ws2c.Cells(2, 1).Value) Then rng.EntireRow.Delete
    End If

By the way, this also does NOT do what you think it does:

Dim ws, ws1c, ws2c, ws3c        As Worksheet

That will declare "ws3c" as a Worksheet type variable, but all the others will be variant. Each variable needs to be declared explicitly, i.e.
like this:

Dim ws As Worksheet, ws1c As Worksheet, ws2c As Worksheet, ws3c As Worksheet

or this:

Dim ws As Worksheet
Dim ws1c As Worksheet
Dim ws2c As Worksheet
Dim ws3c As Worksheet

Note that your code will still work having them declared as variants, but it just doesn't restrict the variable at all then (so you could assign anything to those variables, and not have it restricted to Worksheets).

 

[Alex Blakenburg]

but leaves blank rows (checked by going to a tab and pressing Ctrl and End).

Ctrl+End does not reset after deleting the rows until you either hit Save on the workbook or use the UsedRange property in your code.

In the code, the line below would be enough.

ws2c.UsedRange