How to HIDE a row IF a certain cell is "" blank?

[nenadmail]

Hello... How do I automatically HIDE/UNHIDE a row with code when one cell is blank ""? I know about the filtering, but it doesn't do HIDE/UNHIDE automatically, so that doesn't work.

Thanks!

[jeffreybrown]

Something like this...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[nenadmail]

Okay, it doesn't work for me yet...

This is what I did... I right-clicked on the Worksheet tab and selected "View Code" to open the VBA code for that worksheet. THen I pasted both code into it.

Then when that didn't work, I tried editing the code and in every instance where you wrote "A", I edited to the range "A6:A200" which is the column in question. And that didn't work either...

So the idea is, if anything in cells ("A6:A200") is "" blank, then automatically hide those rows. When those cells are filled again, then unhide.

By the way, those cells have function code in them. But they're set to "" if the function results in a 0.

Thanks again!

[Richard Buttrey]

Hello... How do I automatically HIDE/UNHIDE a row with code when one cell is blank ""? I know about the filtering, but it doesn't do HIDE/UNHIDE automatically, so that doesn't work.

Thanks!

Hi,

It would if you created macro code to filter for non blanks.

[nenadmail]

I don't know how to code that... but I rather not use the "filter" because that filter icon that is created when using it is annoying, and in the way.

By the way, the worksheet and workbook is protected, so keep that in mind for the codes.

[Sa DQ]

Option Explicit
Sub HiddRowsForBlankCellInColumnA()
 Dim hRng As Range
 
 Set hRng = Columns("A:A").SpecialCells(xlCellTypeBlanks)
 If Not hRng Is Nothing Then
    hRng.EntireRow.Hidden = True
    MsgBox "Hidden " & hRng.Count & " rows"
 End If
End Sub 

[jeffreybrown]

By the way, the worksheet and workbook is protected, so keep that in mind for the codes.

So the idea is, if anything in cells ("A6:A200") is "" blank, then automatically hide those rows. When those cells are filled again, then unhide.

Some of these details are good up front.

Call me crazy, but if the row is blank; therefore hidden, how do you enter something in it to unhide?

Please share exactly what you desire...

[nenadmail]

The worksheet is pure formula... and this row A6:A200 will change numbers data, or return "" if blank. Other worksheets that the user controls feed information here.

[nenadmail]

I put the PHP code in the Sheet VBA... and it doesn't do anything. (By the way, there's no need for a Msg Box)...

More info: This page is purely for "Printing"... and I don't want to print rows that return "" blank in A6:A200. It's an order form, and if there is "" blank amount of product to order, I don't want that to print that row. Again, it's a list that is feeding off of another even more detailed worksheet. So, A6:A200 cells are technically not empty, they have formula that is basically "paste link" data from the other worksheet. When no data, it returns "" blank.

[nenadmail]

The "filter" feature almost worked. If there's code to make it constantly update and check for "" blank and constantly hide/unhide... that that will be fine.

[nenadmail]

Okay I did some research, found the code below that works good. However, it only considers NUMBERS not text. So, if a cell contains TEXT and not a number, then it is hidden. Help me fix that please!

Please Login or Register  to view this content.

Also, it will not allow me to just select Col "A"... I have to have a range like A:B... I managed a way around that, but if the code can be fixed that would be better. What I did is add a new column that is always blank, and hid it....

When I edit the code and just put:

Please Login or Register  to view this content.

Then errors come up.

[JosephP]

Please Login or Register  to view this content.

[nenadmail]

I tried it and this is the error I get:

"Run-time error '91':
Object variable or With block variable not set

After I click on "Debug" it highlight in yellow the following:
For Each rRow In rRowRange.Rows

[JosephP]

then you don't have anything in column A of that sheet.

[nenadmail]

You're right... I had added a column for the previous code. BUT NOW IT WORKS PERFECTLY!!! THANK YOU SO MUCH!

[JosephP]

you're welcome. no need to holler though. :-P

[nenadmail]

Oh, that wasn't hollering... I was just trying to make that text stand out more than the first part of the sentence, emphasizing for others that the thread is finally "solved."

[JosephP]

(I was joshing)
ain't that what the mark solved thing is for? ;-)