Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to hide blank rows | Excel Worksheet Functions | |||
hide code does not hide | Excel Discussion (Misc queries) | |||
VB Code to hide and unhide rows | Excel Discussion (Misc queries) | |||
Macro/code to hide rows | Excel Worksheet Functions | |||
Code for button to hide/unhide rows | Excel Worksheet Functions |