Clear Data From Rows under the (LastRow) in in Column A:A where a blank gap exists (Please help!)

[Manerlao]

Hi Excel Community!

I'm having trouble with the following code for a day now. If someone could kindly help this would be great, thank you!

Basically I have a data range and there is some unnecessary data under the last row in the data set.

I have data in the cell range A1:F7 for example, and there is a consistent gap under column A:A under the last row. So I have used this as my unique identifier for the code.
But the code only works to delete a specific range based on the worksheet I have selected.

But I need it to loop through the worksheets and delete the data since the data in the different sheets have different row lengths. For example, the next sheet has data in the range A1:F20.

Sub ClearRows()

Const NumOfRowClear = 100
Dim r As Range, lastrow As Long, ws As Worksheet


lastrow = ActiveSheet.Range("A1").End(xlDown).Row

For Each ws In ActiveWorkbook.Worksheets
Cells.UnMerge
Set r = ws.Range(ws.Cells(lastrow + 1, 1), ws.Cells(lastrow + NumOfRowClear, 10000))
r.ClearContents

Next ws

End Sub

My code includes 'unmerge' because some cells are merged so I unmerge first before running the VBA.

If anyone has any ideas, this would be fantastic! Thank you

Best regards

 

[Fluff]

If you run this

Sub check()
   Dim Msg As String
   Dim ws As Worksheet
   
   For Each ws In Worksheets
      Msg = Msg & ws.Name & vbTab & ws.Range("A1").End(xlDown).Row & vbLf
   Next ws
   MsgBox Msg
End Sub

It will give you a list of your worksheets with a number alongside. Do any of the numbers say 1048576?

 

[Manerlao]

Are any of the sheets protected?

Having no data below A1 was the only way that I could get an error on the same line as you, but if a sheet is protected then it could have a similar effect.

Let me check

 

[Manerlao]

No, all the sheets are unprotected. I checked twice to be sure.
I've hit a wall here ?

 

[Manerlao]

Sub ClearRows()

Dim r As Range, lastrow As Long, ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    ws.Cells.UnMerge
    Set r = Range("A1").End(xlDown).Offset(1)
    r.Resize(100).EntireRow.Delete
Next ws

End Sub

This code works perfectly per sheet. It just doesn't loop through all sheets when I write: Set r = ws.Range("A1").End(xlDown).Offset(1)

It's killing me now..?

 

[jasonb75]

What about Fluff's suggestion for trying to identify the problem?

 

[Manerlao]

If you run this

Sub check()
   Dim Msg As String
   Dim ws As Worksheet
  
   For Each ws In Worksheets
      Msg = Msg & ws.Name & vbTab & ws.Range("A1").End(xlDown).Row & vbLf
   Next ws
   MsgBox Msg
End Sub

It will give you a list of your worksheets with a number alongside. Do any of the numbers say 1048576?

Hi Fluff, I have just seen your message! I completely didn't see it. Let me check now. Thank you

 

[Manerlao]

Hi both, no, the numbers only range up to 30.
The quantity of rows of data is quite limited, so that can't be the issue it seems.

Thank you for this suggestion, it was worth diagnosing. So we can tick that off.

 

[jasonb75]

This code works perfectly per sheet. It just doesn't loop through all sheets when I write: Set r = ws.Range("A1").End(xlDown).Offset(1)

It doesn't show each sheet if that is what you were expecting, it just does its thing in the background.

 

[Fluff]

Ok, is the code located in the same workbook as the sheets, or is it in another workbook?
Also do any of sheets have proper tables?

 

[Manerlao]

Ok, is the code located in the same workbook as the sheets, or is it in another workbook?
Also do any of sheets have proper tables?

Hi both, so I think I know what may be causing the issue. Fluffy, you were on point!

Basically, my code is located in a Masterfile XLSM, but that shouldn't have affect the code functionality from my experience (over the last few weeks). It works for my other workbooks.
Now I realised that I needed a crucial aspect to the Jason's code.

As a summary: Jason your code worked perfectly but I had to run the macro on each sheet for it to clear the 'unwanted' data at the bottom of each table.
This is what I meant Jason.

I adjusted your code to the following:
and now it works really well, thank you Jason!

Thank you both for the help today. I think it works well and it shouldn't have any further hiccups hopefully. )

Sub ClearRows2()

Dim r As Range, lastrow As Long, ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Activate

    On Error Resume Next
    ws.Cells.UnMerge
    On Error GoTo 0
    Set r = Range("A1").End(xlDown).Offset(1)
    r.Resize(100).EntireRow.Delete
Next ws

End Sub