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

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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.

Screenshot.png


VBA Code:
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
 

Attachments

  • Screentshot.png
    Screentshot.png
    12.9 KB · Views: 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,403
Office Version
  1. 365
Platform
  1. Windows
If you run this
VBA Code:
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?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
No, all the sheets are unprotected. I checked twice to be sure.
I've hit a wall here 😐
 

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,045
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
If you run this
VBA Code:
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

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
11,045
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,403
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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! :cool:

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 :giggle: 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. :))


VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,472
Messages
5,548,228
Members
410,824
Latest member
Bobmn4
Top