Code to Hide Rows

[wx4usa]

Can anyone provide code to hide entire rows if cells contain no data/
values?

For example in my chart of accounts, I have 150 accounts. Column A is
Budget, B is Actual. If Column B row 76 (Cellb76) has no value, I'd
like to hide the entire row. This way, I only see rows with values for
the month.

Then secondly, if that works easily, can Excel look at both A76 and
B76 and if no values hide row?

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it. If
Column A & Column B are empty then the entirerow is hidden

Sub Liminal()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
If IsEmpty(c) And IsEmpty(c.Offset(, 1)) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.EntireRow.Hidden = True
End If

End Sub

Mike

 

[David Biddulph]

The easy way is probably to use Auto-filter (and for example select
non-blank in column B). If there isn't a column to use directly, you can
add a helper column, so perhaps =COUNT(A76,B76), and filter by that.

 

[Don Guillett]

This will hide all rows if both col A and col B are truly blank

Sub hiderowsifnovalue()
Rows.Hidden = False
Application.ScreenUpdating = False
mc = 1
Lr = Cells(Rows.Count, mc).End(xlUp).Row
For i = Lr To 2 Step -1
If Len(Application.Trim(Cells(i, mc))) < 1 Or _
Len(Application.Trim(Cells(i, mc + 1))) < 1 Then
Rows(i).Hidden = True
end if
Next i
Application.ScreenUpdating = True
End Sub

 

[Don Guillett]

Does it unhide those that were previously hidden?
Does it hide those where someone may have touched the spacebar?


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi,

Right click your sheet tab, view code and paste this in and run it. If
Column A & Column B are empty then the entirerow is hidden

Sub Liminal()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
If IsEmpty(c) And IsEmpty(c.Offset(, 1)) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.EntireRow.Hidden = True
End If

End Sub

Mike

 

[Mike H]

Does it unhide those that were previously hidden?

Why ask a question you know the answer to? Did the OP ask for that?

Does it hide those where someone may have touched the spacebar?

Once again, Why ask a question you know the answer to?

Mike

 

[wx4usa]

Hi,

Right click your sheet tab, view code and paste this in and run it. If
Column A & Column B are empty then the entirerow is hidden

Sub Liminal()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
If IsEmpty(c) And IsEmpty(c.Offset(, 1)) Then
        If MyRange1 Is Nothing Then
            Set MyRange1 = c.EntireRow
        Else
            Set MyRange1 = Union(MyRange1, c.EntireRow)
        End If
    End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.EntireRow.Hidden = True
End If

End Sub

Mike

Thanks All, I will try these and let you know how it does. Thank you
very much and Happy New Year!

 

[Don Guillett]

Mike,
I didn't mean to offend you. People are always amending my code and I don't
mind a bit. Harlan is a bit rough sometimes but that's just Harlan. Perhaps
I should have worded it better.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Does it unhide those that were previously hidden?

Why ask a question you know the answer to? Did the OP ask for that?

Does it hide those where someone may have touched the spacebar?

Once again, Why ask a question you know the answer to?

Mike

 

[Mike H]

Don,

Absolutely no offence taken, I welcome advice on improving whatever
limited knowledge I have, virtually all of which gained from posting
and reading in these forums and responding to corrections.

Mike