How to hide rows if cells are empty

[hond70]

Hi there,

Could somebody help me to write code for a macro that hides all rows in which a certain cell is empty:

Column A - Column B - Column C
1 Philips - 1000000 - 200 mln
2 Shell
3 Lever
4 Nutricia - 5400000 - 240 mln

I would like a macro that hides row 2 (and 3) when B2 (and B3) are empty.
Also I need a macro that hides row 2 (and 3) when range B2:C2 (and B3:C3) are empty.

Next I need a macro that is a updater (looks if hidden rows are not empty anymore and unhides them

The last macro unhides all hidden rows whether or not the cell is empty.

Please advice! Thanks in advance.

 

[tactps]

Note that these are untested:

Macro 1:

sub macro1()
range("b2").select
do while activecell.row < 100
if activecell.value = "" and activecell.offset(0,1).value = "" then
Selection.EntireRow.Hidden = True
endif
activecell.offset(1,0).select
loop
end sub

Macro 2:

sub macro1()
range("b2").select
do while activecell.row < 100
if activecell.value = "" and activecell.offset(1,0).value = "" then
Selection.EntireRow.Hidden = True
endif
activecell.offset(1,0).select
loop
end sub

Macro 3:

Cells.Select
    Selection.EntireRow.Hidden = False

I hope that this helps

 

[hond70]

Wow, the first and the last macros work like a charm ray:

Unfortunately, the second one (the updater) doesn't work. It supposed to check all cells again and hide only the empty ones, or in other words: it supposed to check all cells again (F9) and unhide cells that now contain data.

Thanks anyway! This is great.

 

[HalfAce]

You should be able to get away with just these two...

Sub Updater()
Cells.EntireRow.Hidden = False
For Each cell In Range("B2", Range("B65536").End(xlUp))
If cell = "" And cell.Offset(, 1) = "" Then cell.EntireRow.Hidden = True
Next cell
End Sub

What it does is unhides all rows, checks to see if the cells in columns B & C are blank and if so, re-hides just those rows.
And

Sub UnhideAll()
Cells.EntireRow.Hidden = False
End Sub

This what you're looking for?

 

[tactps]

Alternatively, for macro 2:

sub macro2()
Cells.Select
Selection.EntireRow.Hidden = False
range("b2").select
do while activecell.row < 100
if activecell.value = "" and activecell.offset(1,0).value = "" then
Selection.EntireRow.Hidden = True
endif
activecell.offset(1,0).select
loop
end sub

You can also add those 1st 2 lines into macro 1.

Regards

 

[Legacy 21301]

Provided that the sheet's used range always extends past column B, it could be done with a "one-liner" (no loop) :-

Sub Updater()
Cells.EntireRow.Hidden = False
On Error Resume Next
Range([A1], [A65536].End(xlUp)).Offset(, 1).SpecialCells(xlCellTypeBlanks).Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0
End Sub

Note : This assumes that you are looking to hide cells that are blank, and not cells that contain "" as a result of a formula.

 

[scoha]

Hiding rows when blank

But how do you modify this such that it only hides rows between a specific range (say a50 through to a100) which are empty.

I have a worksheet where the central part is populated through lookup formula between rows 50 and 100 and I want to blank out the rows in that range where the lookup returns no results

Any ideas??

 

[ssingh75]

Ok..What if columns range is more than 10. then can we hide entire row if there is no data in cell.

Thanks