CODEWITHSUNDEEP
vbaexcel
Matt Taylor
Matt Taylor

Reputation: 671

excel hide empty cells in range

I am trying to do something simple and hide a row if all data in that row is empty. This is what i have so far.

Sub UpdateFields_630()
Application.ScreenUpdating = False

Dim sht3 As Worksheet
Set sht3 = ThisWorkbook.Worksheets("630 BOM")

Dim LastRow As Long, LastCol As Long
Dim rng As Range, c As Range

On Error GoTo 0

With sht3
    Set rng = Cells

    LastRow = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

    LastCol = rng.Find(What:="*", After:=rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

    For Each c In Range(Cells(9, "E"), Cells(LastRow, LastCol))
    If c.Value = "" Then
        c.EntireRow.Hidden = True
    Else
        c.EntireRow.Hidden = False
    End If
    Next
End With

sht3.Protect

Set rng = Nothing
Set sht3 = Nothing
Application.ScreenUpdating = True
End Sub

Before Sort

before

After Sort

after

Rows 13,14,19,20 and 38 are being hidden with this code for some reason and can't figure out why.

I can get this to work if i hide based on column "A" total = 0 but then row rows 27 & 30 will hide. I've tried If c.Value = "x" Then c.EntireRow.Hidden = False and that didn't seem to do anything.

Thanks for any help.

Upvotes: 1

Views: 172

Answers (1)

A.S.H
A.S.H

Reputation: 29332

1- You're hiding the row if any of its cells is empty, not if all of them are empty

2- You're not qualifying your ranges, making your with clause useless.

You can use Application.CountA to check if all the cells of a range are empty. Apply it on each row to decide if it should be hidden.

    '             v            v (notice these dots)
    For Each c In .Range("E9", .Cells(LastRow, LastCol)).Rows
        c.EntireRow.Hidden = Application.CountA(c) = 0
    Next

EDIT

since the blank cells have formula, CountA wont count then as blank. For this reason use this instead:

    For Each c In .Range("E9", .Cells(LastRow, LastCol)).Rows
        c.EntireRow.Hidden = Application.CountIf(c, "") = c.Cells.Count
    Next

Upvotes: 1

Related Questions