Hide rows in a range if cell is blank (has formulas, but no value)

[sdoppke]

Well the title pretty much said it all. I am looking have a macro that hides the rows in a column range. Not based on the entire column, but say if there are cells within A25:A50 that are empty (all will have formulas) then hide entire row of those empty cells.

I wont be able to use autofilter, because there is no consitency in the cloumns or rows.

Thanks a ton in advance.

sd

 

[sdoppke]

Hi sd,

Could you try this macro?

Just run it normally and see if it colors yellow the correct rows you want hidden. If so then just replace the part in red by ".hidden=true" (without the quotes)

Sub hide()
Dim lr As Long, e As Range
lr = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
For Each e In Range("A1:A" & lr).SpecialCells(xlFormulas)
 If Len(e) = 0 Then Rows(e.Row).Interior.Color = vbYellow
Next
On Error GoTo 0
End Sub

Thnank you again for replying and hangin in there. Its weird, no cells turn yellow. See the macro below. I have tried it on only 10 rows (A23:A32) in Column A. These 10 cell are blanck except they contain formulas, and yet they still do not turn yellow. Any ideas where im going wrong?

Sub hide()
Dim lr As Long, e As Range
lr = Range("A" & Rows.Count).End(xlUp).row
On Error Resume Next
For Each e In Range("A23:A32" & lr).SpecialCells(xlFormulas)
 If Len(e) = 0 Then Rows(e.row).Interior.Color = vbYellow
Next
On Error GoTo 0
End Sub

 

[mirabeau]

Hi,

If you want to pre-specify the range to be used, you should use

Range("A23:A32")
rather than
Range("A23:A32" & lr)

although unlikely this is the reason for no yellow showing.

I guess the term "blank" in Excel has a bit of flexibility depending in who is using it and what is really in the cell(s).

When I tested that code I just used simple formulas such as
=IF(1=2,"","")
where a "blank" cell is one that displays "".
The code worked OK for me in such case, because of the

IF len(e) = 0 ...

line.

However there's other ways of getting blank cells, such as if you use the formula =IF(1=2,char(160),char(160)) or similar, such as just =Char(160).

The non-printing character Char(160) (or Chr(160) in VBA) is one that sometimes causes problems when looking at a blank cell it is also assumed to be empty.

However, my short answer is that my code as posted only looks at one version of a "blank" cell. I just don't have time for a while to look at the problem more closely for a while, but if you don't get other answers then I'll have another look at it.

As a very quick attempt, you might rerun the code with the relevant line changed to

If Len(e) = 0 Or e=Chr(160)Then Rows(e.Row).Interior.Color = vbYellow

although that may or may not work for you.