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!
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!
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
HTH
Regards, Jeff
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
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
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.
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.
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:
Then errors come up.![]()
'< Set the columns that may contain data > Const FirstCol As String = "A" Const LastCol As String = "A"
Last edited by nenadmail; 05-23-2012 at 06:25 AM.
PHP Code:
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
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.
Last edited by nenadmail; 05-23-2012 at 03:08 AM.
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.
Last edited by nenadmail; 05-23-2012 at 03:08 AM.
![]()
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
Josie
if at first you don't succeed try doing it the way your wife told you to
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
then you don't have anything in column A of that sheet.
You're right... I had added a column for the previous code. BUT NOW IT WORKS PERFECTLY!!! THANK YOU SO MUCH!
you're welcome. no need to holler though. :-P
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."
(I was joshing)
ain't that what the mark solved thing is for? ;-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks