Remove Blank Rows from all Sheets Macro Help!

Hi all,

First of all hello, I'm new to this forum and just about as new to VBAs and Macros! I'm hoping I can get some advice...

I have a workbook which contains 48 sheets. these 48 sheets have 2 lists of data varying length, that formulate from a master workbook. Each week the length of the lists change depending on the info input into the master. I then have a macro that saves each of these sheets into individual pdfs.

I have a code that works like a dream to remove all the blank rows on my first sheet- what I would love is if this could run through all of the 48 sheets, not just the first!

How do I add to my code "all sheets" rather than just the sheet in on?

I've spent the last 2 days Googling but I'm hitting a wall!


For info, the first list starts at C7 and is 9 columns long. I give about 100 rows to both lists just in case (the second starts beneath the first at about C107) and the "blanks" have forumla in but appear blank, my below code doesn't seem to mind this

My code is:

Sub DeleteBlankRows()
Application.ScreenUpdating = False

Dim r As Long, lr As Long

lr = Cells(Rows.Count, "c").End(xlUp).Row

Do Until r = lr
 r = r + 1
 If Range("C" & r).Value = "" Then
 Rows(r).Delete
 r = r - 1
 lr = lr - 1
 End If
 Loop
 
Application.ScreenUpdating = True
End Sub

I've attached an example workbook that has the same layout as what I'm after!

Any help here?
TIA legends!

I have added code tags for you, as you are new. Please review the forum guidelines.

Try

Option Explicit

Sub DeleteBlankRow()
  Dim ws As Worksheet
  Dim rng As Range
  Dim lr As Long
  For Each ws In ThisWorkbook.Worksheets
    On Error Resume Next
    lr = ws.Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = ws.Range("C2:c" & lr)
    rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  Next ws
End Sub

Post a sample workbook.

It removes any rows where column C cell is blank: not what you want?

I don't know if this is the case in your workbook but a common problem is that cells look to be empty but have a non printable character in it (space is one of the possibilities)

If you run this on each sheet it should show which ranges are empty.

Sub Check_So()
Dim c As Range
For Each c In Range("C6:C" & Cells(Rows.Count, 3).End(xlUp).Row)
    If WorksheetFunction.CountA(Range(Cells(c.Row, 2), Cells(c.Row, 10))) = 0 Then c.Offset(, 8).Value = "Empty"
Next c
End Sub

Or you can run this to see how many cells in that Range are not empty.

Sub Check_So_2()
Dim c As Range
For Each c In Range("C6:C" & Cells(Rows.Count, 3).End(xlUp).Row)
    c.Offset(, 8).Value = WorksheetFunction.CountA(Range(Cells(c.Row, 2), Cells(c.Row, 10)))
Next c
End Sub