Code to Hide Rows
 

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?

Code to Hide Rows
 

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

On Dec 27, 3:04*am, wx4usa wrote:
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?

Code to Hide Rows
 

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.
--
David Biddulph

"wx4usa" wrote in message
...
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?

Code to Hide Rows
 

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
Microsoft MVP Excel
SalesAid Software

"wx4usa" wrote in message
...
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?

Code to Hide Rows
 

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

"Mike H" wrote in message
...
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

On Dec 27, 3:04 am, wx4usa wrote:
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?

Code to Hide Rows
 

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

On Dec 27, 4:46*am, "Don Guillett" wrote:
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
"Mike H" wrote in message

...
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

On Dec 27, 3:04 am, wx4usa wrote:



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?- Hide quoted text -

- Show quoted text -

Code to Hide Rows
 

On Dec 26, 9:16*am, Mike H wrote:
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

On Dec 27, 3:04*am, wx4usa wrote:

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?

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

Code to Hide Rows
 

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

"Mike H" wrote in message
...
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

On Dec 27, 4:46 am, "Don Guillett" wrote:
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
"Mike H" wrote in
message

...
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

On Dec 27, 3:04 am, wx4usa wrote:



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?- Hide quoted text -

- Show quoted text -

Code to Hide Rows
 

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

On Dec 27, 6:26*am, "Don Guillett" wrote:
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
"Mike H" wrote in message

...

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

On Dec 27, 4:46 am, "Don Guillett" wrote:



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
"Mike H" wrote in
message

...
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

On Dec 27, 3:04 am, wx4usa wrote:

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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -