Hide rows in multiple columns when cell is empty

[Bolter LAC]

Hello again.

I have a macro button that when activated deletes all rows with a blank cell in column 'A'. I now want insert new columns and change the macro to delete rows with blank cells to columns 'A' through to 'F'. I have tried to alter the range in the below macro but when I activate the button the macro deletes everything below A6. Any help appreciated.

Sub HideRows()

Range("a6:a600").EntireRow.Hidden = False
On Error Resume Next
Set Rng = Range("a6:a600").SpecialCells(xlBlanks)
On Error GoTo 0
If Not Rng Is Nothing Then
Rng.EntireRow.Hidden = True
End If
End Sub

Thanks
Bolter LAC

 

[DanteAmor]

Try this:

Sub HideRows()
  Dim rng As Range
  Dim i As Long, n As Long
  
  Application.ScreenUpdating = False
  Range("a6:a600").EntireRow.Hidden = False
  For i = 6 To 600
    n = WorksheetFunction.CountBlank(Range("A" & i & ":I" & i))
    If n = 9 Then
      If rng Is Nothing Then
        Set rng = Range("A" & i)
      Else
        Set rng = Union(rng, Range("A" & i))
      End If
    End If
  Next
  If Not rng Is Nothing Then
    rng.EntireRow.Hidden = True
  End If
End Sub

 

[Bolter LAC]

Hi Dante,

No luck. When I run the macro nothing happens. There is no error code or anything. The original code worked for column 'A' however I have now inserted additional columns preceding 'A' and tried to change the range but no success. Thanks for trying.

 

[DanteAmor]

You really have empty cells A through I.
Check if you have formulas or cells with blank spaces, it is not the same empty as blank spaces.

You could put an imange or a minisheet with XL2BB tool to verify that you really have empty from A to I, let's say rows 8 and 9