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

Sub hiderows()
    Dim LR As Long
    Dim i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To LR
        If Len(Range("A" & i)) = 0 Then
            Rows(i).EntireRow.Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

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

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

[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 "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!

Option Explicit
 
Private Sub Worksheet_Activate()
     
    Dim HiddenRow&, RowRange As Range, RowRangeValue&
     
     '*****************************
     '< Set the 1st & last rows to be hidden >
    Const FirstRow As Long = 6
    Const LastRow As Long = 200
     
     '< Set the columns that may contain data >
    Const FirstCol As String = "A"
    Const LastCol As String = "B"
     '*****************************
     
    ActiveWindow.DisplayZeros = False
    Application.ScreenUpdating = False
     
    For HiddenRow = FirstRow To LastRow
         
         '(we're using columns B to G here)
        Set RowRange = Range(FirstCol & HiddenRow & _
        ":" & LastCol & HiddenRow)
         
         'sums the entries in cells in the RowRange
        RowRangeValue = Application.Sum(RowRange.Value)
         
        If RowRangeValue <> 0 Then
             'there's something in this row - don't hide
            Rows(HiddenRow).EntireRow.Hidden = False
        Else
             'there's nothing in this row yet - hide it
            Rows(HiddenRow).EntireRow.Hidden = True
        End If
         
    Next HiddenRow
     
    Application.ScreenUpdating = True
     
End Sub

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:

     '< Set the columns that may contain data >
    Const FirstCol As String = "A"
    Const LastCol As String = "A"

Then errors come up.

[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 

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

[JosephP]

Private Sub Worksheet_Activate()

    Dim rRow             As Range
    Dim rRowRange        As Range
    Dim rCell            As Range
    Dim bHide            As Boolean
    
    ' change as you need
    Set rRowRange = Intersect(UsedRange, Range("A:A"))

    ActiveWindow.DisplayZeros = False
    Application.ScreenUpdating = False

    For Each rRow In rRowRange.Rows
        bHide = False
        For Each rCell In rRow.Cells

            If Len(rCell.Value) = 0 Then
                bHide = True
                Exit For
            End If
        Next rCell
        rRow.EntireRow.Hidden = bHide

    Next rRow

    Application.ScreenUpdating = True

End Sub

[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? ;-)