Code to Hide Rows

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

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?
 
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
 
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
 
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
 
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?
Click to expand...

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

Mike
 
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
Click to expand...

Thanks All, I will try these and let you know how it does. Thank you
very much and Happy New Year!
 
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)
Mike H said:
Does it unhide those that were previously hidden?
Click to expand...

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?
Click to expand...

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

Mike
 
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
 
Back
Top