locked
Hide rows in active sheet if cells are blank RRS feed

  • Question

  • I am looking to hide rows if the cell within the row is blank from A1:A299. So if A1 is blank hide that row, if cell A2 is blank hide that row and so on.

    Also, is there a way to make the script continue to work automatically?

    More info just in case it is needed:

    The data that is in this sheet is being auto-populated from another sheet however some rows are blank if they have a certain value and those are the rows that I am trying to hide.

    Hopefully this makes sense and I thank you in advance for any help!


    Dustin Shepard

    Friday, June 28, 2013 6:32 PM

Answers

  • There might be more efficient ways to do this, depending on exactly how your are "auto-populating" your worksheet, and exactly what you mean by "blank", but the following scheme should work.  It runs a macro that hides the rows where column A is blank whenever you activate the worksheet.

    If the worksheet can be active when the cells get changed, you might also need a worksheet_change event macro, but try this first.

    To enter this event-triggered Macro, right click on the sheet tab.
    Select "View Code" from the right-click drop-down menu.
    Then paste the code below into the window that opens.

    Option Explicit
    Private Sub Worksheet_Activate()
        Dim r As Range, c As Range
    Set r = Range("a1:a299")
    Application.ScreenUpdating = False
    For Each c In r
        If Len(c.text) = 0 Then
            c.EntireRow.Hidden = True
        Else
            c.EntireRow.Hidden = False
        End If
    Next c
    Application.ScreenUpdating = True
    End Sub


    Ron


    • Edited by Ron Rosenfeld Saturday, June 29, 2013 12:12 PM update code and comment
    • Proposed as answer by ProDevX Saturday, June 29, 2013 6:45 PM
    • Marked as answer by Dustin Shepard Monday, July 1, 2013 6:35 PM
    Saturday, June 29, 2013 12:10 PM

All replies

  • Sorting automatically moves blank cells to the bottom.
    Click the row header to select the column then click the sort button - you're done.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Saturday, October 29, 2016 1:39 AM
    Friday, June 28, 2013 11:46 PM
  • Thank you however sorting will not work for this as data will change and dont want to constantly sort 

    Dustin Shepard

    Friday, June 28, 2013 11:52 PM
  • There might be more efficient ways to do this, depending on exactly how your are "auto-populating" your worksheet, and exactly what you mean by "blank", but the following scheme should work.  It runs a macro that hides the rows where column A is blank whenever you activate the worksheet.

    If the worksheet can be active when the cells get changed, you might also need a worksheet_change event macro, but try this first.

    To enter this event-triggered Macro, right click on the sheet tab.
    Select "View Code" from the right-click drop-down menu.
    Then paste the code below into the window that opens.

    Option Explicit
    Private Sub Worksheet_Activate()
        Dim r As Range, c As Range
    Set r = Range("a1:a299")
    Application.ScreenUpdating = False
    For Each c In r
        If Len(c.text) = 0 Then
            c.EntireRow.Hidden = True
        Else
            c.EntireRow.Hidden = False
        End If
    Next c
    Application.ScreenUpdating = True
    End Sub


    Ron


    • Edited by Ron Rosenfeld Saturday, June 29, 2013 12:12 PM update code and comment
    • Proposed as answer by ProDevX Saturday, June 29, 2013 6:45 PM
    • Marked as answer by Dustin Shepard Monday, July 1, 2013 6:35 PM
    Saturday, June 29, 2013 12:10 PM
  • Hi Ron,

    I tried this but did not succeed... maybe it is because I have formula in the cells for which the result is empty, example:

    =IF(Overview!B17="";"";Overview!B17)

    in my case I need to hide the line if both column D AND column E is empty, can I write d22:e100 or is it more complicated?!

    I hope you can help,

    Mosdeb

    Tuesday, December 31, 2013 10:54 AM
  • How did you adapt the code to your particular problem?  What were the results?


    Ron

    Tuesday, December 31, 2013 1:11 PM
  • I used VBA and this is the code I used:

    Option Explicit
    Private Sub Worksheet_Activate()
        Dim r As Range, c As Range
    Set r = Range("a21:a64")
    Application.ScreenUpdating = False
    For Each c In r
        If Len(c.Text) = 0 Then
            c.EntireRow.Hidden = True
        Else
            c.EntireRow.Hidden = False
        End If
    Next c
    Application.ScreenUpdating = True
    End Sub


    Dustin Shepard

    Tuesday, December 31, 2013 5:25 PM
  • There are some things I am not understanding.  You write about wanting to check columns D & E, yet I see nothing in the code you are using that refers to that range.

    If you want to adapt the code I provided to check, for example, D21:E64, you'll need to do something like:

    Option Explicit
     Private Sub Worksheet_Activate()
         Dim r As Range, c As Range
     Set r = Range("d22:e100")
     Application.ScreenUpdating = False
     For Each c In r.Rows
         If Len(c.Cells(1).Text) + Len(c.Cells(2).Text) = 0 Then
             c.EntireRow.Hidden = True
         Else
             c.EntireRow.Hidden = False
         End If
     Next c
     Application.ScreenUpdating = True
     End Sub
    
    And don't forget this approach assumes the cells in the affected area are populated from elsewhere.


    Ron

    Tuesday, December 31, 2013 7:56 PM
  • Hi Ron, Hi Dustin,

    Thanks for your answers & sorry if I was not clear. I tried both of your codes "just like this" but they did not work: the line without infos in e and f did not disappear (for Ron's code).

    I am not used to work with VBA, so I probably did a mistake, I just did as Ron wrote:

    Select "View Code" from the right-click drop-down menu.
    Then paste the code below into the window that opens.

    Then, I just clicked on save. Should not I define something special?!

    Ron, what do you mean by populating?! I am "populating" them using the following formula in each cells of the spreadsheet where I want to hide the empty lines:

    =IF(Overview!B17="";"";Overview!B17)

    So I get empty cells if in this case B17 is empty in the sheet called Overview, but I have a formula in my cell!

    So, I deleted the formulas from D33:E34 (as an example, so that they are really empty), but the lines still appear... so I must definitely be misusing VBA!

    Any idea?!

    Mosdeb

    Thursday, January 2, 2014 11:00 AM
  • Which sheet tab did you "right click" on?

    Where did you find a "Save" option?  (I did not specify that in my instructions and it should not be necessary, until you are ready to save the entire workbook, of course).


    Ron

    Thursday, January 2, 2014 4:16 PM
  • I have done as follows:

    (note you see the code under C22)

    and then I pasted your text changing the lines (because in this sheet it is c22:d100)

    and clicked on save:

    and nothing happens... (line 22 to 29 and 30 to 100 should be hidden)

    should not I define anything more?!

    Maybe there could be an incompatibility between versions?! I saw on many forums that they write commas instead of semi-colon: =IF(Overview!V18="","",Overview!V18) instead of =IF(Overview!V18="";"";Overview!V18)

    commas in my version are not accepted.

    I am working with Microsoft Excel 2010 (14.0.7109.5000),

    Do you think this could this be the reason?

    Friday, January 3, 2014 8:15 AM
  • Did you select another worksheet, and then re-select this worksheet C54?  The macro is designed to run when the worksheet is activated. (note the Worksheet_Activate line at the top!).

    Commas vs semicolon separators are merely a function of different international settings and shouldn't have any affect on this macro, as far as I know.


    Ron

    Friday, January 3, 2014 10:45 AM
  • Great, I have tried a littlle further, looking at this activeate line and it works!!! :-D

    Thanks a lot!!!

    I have one little thing which I would like to combine: if c14=c15 hide line 15

    note that in c14 and c15 I have dates I do not think that if it makes a difference but I prefer to say it, can you add this to this scipt please?!

    thank you so much!

    Friday, January 3, 2014 2:20 PM
  • You can just add a line above the For ... Next loop.  Something like:

    Application.ScreenUpdating = False If range("c14") = range("c15") then
    range("c15").entirerow.hidden = true
    Else
    range("c15").entirerow.hidden = false
    End if

    For Each c In r.Rows ... Next c

    (Not debugged, so check it).

    Ron

    Friday, January 3, 2014 6:15 PM
  • Thanks Ron, it works perfectly!!! :-D :-D :-D

    Tuesday, January 7, 2014 8:23 AM
  • Hi, 

    I adopted your code. But I ran into a couple issues. I'm hoping you can give me some advice on how to fix it.

    For my case, I was trying to hide the cells value of which are 0 in multiple ranges.The cell value are based on a few conditions; the values will change if I change the conditions. this code won't unhide the ones whose value were 0 before I change the conditions and have a non 0 value after I change the conditions. The other issue I have is that it also hides rows between O61 and O66 when I set the range as Set r = Range("O8:O61", "O66:O119")

    Below is my code for your reference,

    Private Sub Worksheet_Active()
    Dim r As Range, c As Range
    Set r = Range("O8:O61", "O66:O119")
    Application.ScreenUpdating = False
    For Each c In r
    If c = 0 Then
    c.EntireRow.Hidden = True
    Else
    c.EntireRow.Hidden = False
    End If
    Next c
    Application.ScreenUpdating = True

    End Sub

    Thanks in advance,

    Lu

    Tuesday, June 17, 2014 4:26 PM
  • I am not aware of Worksheet_Active().  Perhaps you are using a newer version of Excel than I as there is no such event in the list for VBA and Excel 2007.  Or maybe you have a non-English version and that is a translation?

    For what you want, you probably need to use the Change event, or whatever takes its place in your version of Excel.

    Your syntax for your Range argument is wrong for what you want to do.  Help is confusing, but Range will not return a multi-area union, they way you are using it.  Perhaps something like:

    Set R = Union([O8:O61], [O66:O119])

    Depending on the precedents of your cells in R, you may be able to trigger the macro to run only when one of those precedents change.  If you use the worksheet change event it will trigger on every change.

    In addition to turning off screen updating; if you are using a Change event, you should also set EnableEvents to False when the macro is running; and then reset it to true when you're done.  Otherwise it will trigger excessively.


    Ron

    Tuesday, June 17, 2014 7:42 PM
  • Hi Ron,

    Thanks for your response. I'm using an English 2010 Excel.

    The union function worked out well.

    I tried to change it to worksheet_Change(see code below); but it gives an error message, compile error: Ambiguous name detected: Worksheet_Change.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, c As Range
    Set r = Union([O8:O61], [O66:O119])
    Application.ScreenUpdating = False
    For Each c In r
    If c = 0 Then
    c.EntireRow.Hidden = True
    Else
    c.EntireRow.Hidden = False
    End If
    Next c
    Application.ScreenUpdating = False

    End Sub

    Thanks,

    Lu


    • Edited by lshen89 Thursday, June 19, 2014 4:33 PM
    Thursday, June 19, 2014 4:32 PM
  • You probably put it in the wrong spot.  Or you have another Worksheet_change event macro.

    Ron

    Thursday, June 19, 2014 5:06 PM
  • Yes, I do have another worksheet_change event.

    So I tried to name them change1 and change2, then neither of them worked.

    Thursday, June 19, 2014 6:18 PM
  • AFAIK, you'll have to combine the functionality that you want into a single worksheet_change event macro.

    Ron

    Thursday, June 19, 2014 7:33 PM
  • Hi Ron,

    I would like to be able to protect the sheet where these lines have to disappear so that peaple cannot change them. I tried but it makes a bug with this macro... any idea how to handle this?!

    KR

    Mosdeb

    Thursday, September 18, 2014 12:48 PM
  • you have to unprotect the sheet when the macro runs.

    Ron

    Friday, September 19, 2014 2:05 AM
  • You deserve a damn medal man, thank you!
    Saturday, January 16, 2016 8:25 PM
  • Dear Mr. Ron, this code is not working.. can you please help me ? Is this code work with Excel 2007 ?
    • Edited by Raisan Tuesday, January 26, 2016 12:04 PM something not mention
    Tuesday, January 26, 2016 12:03 PM
  • Probably.

    Yes.


    Ron

    Tuesday, January 26, 2016 12:31 PM
  • Hi Ron,

    Considering you've been answering questions on this thread for three years I'm going to have to agree with the person who said you deserve a medal! Thank you in advance!

    This is literally my first time working with macros/VBA, so please excuse my ignorance.

    I have an excel sheet that is a list of properties I'm populating via an =if() statement that links to a master list. I have already input formulas to only bring in property codes when the date corresponding to that property is within a certain range.

    I'm trying to use macros to auto hide any rows that do not populate (which would happen if the date did not trigger that particular property to populate this secondary sheet).

    I took this code I found on another forum.

    Sub HideRows()
        BeginRow = 1
        EndRow = 811
        ChkCol = 2
    
        For RowCnt = BeginRow To EndRow
            If Cells(RowCnt, ChkCol).Value < 1 Then
                Cells(RowCnt, ChkCol).EntireRow.Hidden = True
            End If
        Next RowCnt
    End Sub

    The goal is to check rows 1-811, and hide all rows with a blank in column 2 (where column two is a numerical property code, and meaning the property code is being pulled in to column 2 in this secondary sheet because the date corresponding with that property code on the master sheet is within the range that I'm interested in). Could the fact that column 2 is being pulled in by an =If() statement make the macro not work? I've saved as macro enabled worksheet, went to macro security and enabled macros, and put the code in to the VBA window, clicked on run macro.... and nothing happens.

    Any thoughts you have would be much appreciated. Thanks for taking the time to help the people who don't know what we're doing!

     



    • Edited by JessMiami Tuesday, September 27, 2016 12:43 PM
    Tuesday, September 27, 2016 12:40 PM
  • How did you navigate to the "VBA Window"?

    What is the formula in the cells in column 2

    Your code looks OK (and runs OK here with nothing or with an If statement in Column 2 (B), so the problem is likely elsewhere.


    Ron

    Tuesday, September 27, 2016 3:09 PM
  • Hi Ron,

    Your formula worked great with me!

    However, the sheet to which I have applied this macro to is not getting auto-populated from another sheet, unlike Dustin's sheet. I am actually entering data from a data validation list on that same sheet. So, I would like to see all the blanks cells so I can enter data in some of those blank cells. 

    It's only when I want to print that I'd like all the blanks to be hidden. Is there a chance you can help me modify this macro to my needs? I have tried using a hide blanks macro from Google, but it only works for when blanks are at the bottom of my sheet; however, the blanks on my sheet are randomly spread out in the spreadsheet. For example, A7 to A10 is not blank, but A11 to A17 is blank, and A20 & A21 are not blank.

    Your help in this matter will be very much appreciated. This is my first Excel help post, so I'm sorry if I'm breaking any forum rules.
    Thanks
    Omar

    Wednesday, February 8, 2017 2:32 AM
  • Instead of putting the code into  Worksheet_Activate event code, merely use a Workbooks_BeforePrint code.  And you'll probably want to unhide the rows after you print.

    Ron

    Wednesday, February 8, 2017 3:43 AM
  • Ron,

    Hoping you are still monitoring this forum and that you could help out a fellow Ron....I have the following code in a spreadsheet and it does not work.  Can't figure out what is wrong, as it seems pretty straight forward.  I want to look in worksheets P20 thru P27 to see if there are values >0 in cells D13:D29.  If there are not I want to hide the row.

    Sub HideRows()
        Dim i As Integer, ws As Worksheet, r As Range
       
        Application.ScreenUpdating = False
       
        For i = 20 To 27
            Set ws = Sheets("P" & i)
            For Each r In ws.Range("D13:D29")
                If Not r > 0 Then r.EntireRow.Hidden = True
            Next r
        Next i
        Application.ScreenUpdating = True
    End Sub

    I'm not getting any error messages it just doesn't do anything.  Again, hoping you're still around and could help.  Thanks.

    Ron

    Tuesday, August 1, 2017 9:26 PM
    • Into what kind of module did you enter the code?
    • What is the contents of D13:D29 that is not getting hidden
    •      There are many things other than numbers that Excel sees as ">0"

    With empty rows, and values  > 0  and  < 0 your code works fine here.


    Ron

    Wednesday, August 2, 2017 11:31 AM
  • I was using it in conjunction with another code that would save the resulting pages into a PDF, if the value in cell Z1 on each page was 1.  If 0, it would not include those pages into the pdf.  The values for cells D13:D29 are if statements:   =IF(K10="Small",ROUND(K14*c5,2),ROUND(K14*B5,2)) It appears that the result if not a positive value is 0.

    The full code is as follows and I have both of these codes together in "Module3".

    Sub HideRows()
        Dim i As Integer, ws As Worksheet, r As Range
       
        Application.ScreenUpdating = False
       
        For i = 20 To 27
            Set ws = Sheets("P" & i)
            For Each r In ws.Range("D13:D29")
                If Not r > 0 Then r.EntireRow.Hidden = True
            Next r
        Next i
        Application.ScreenUpdating = True
    End Sub

    Sub Export_Sheets_To_PDF()

        Dim PdfFilename As Variant
        Dim CurrentSheet As Worksheet, ws As Worksheet
        Dim ReplaceSelected As Boolean
           
        PdfFilename = Application.GetSaveAsFilename( _
            InitialFileName:="ETF Proposal_", _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Save As PDF")
       
       
        If PdfFilename <> False Then
       
            With ThisWorkbook

                Set CurrentSheet = ActiveSheet

                ReplaceSelected = True
                For Each ws In .Worksheets
                    If (ws.Name Like "P#" Or ws.Name Like "P##") And ws.Range("Z1").Value = 1 Then
                     ws.Select ReplaceSelected
                     ReplaceSelected = False
                    End If
               Next
               
                .ActiveSheet.ExportAsFixedFormat _
                    Type:=xlTypePDF, _
                    Filename:=PdfFilename, _
                    Quality:=xlQualityStandard, _
                    IncludeDocProperties:=False, _
                    IgnorePrintAreas:=False, _
                    OpenAfterPublish:=True
               
                CurrentSheet.Select True
               
            End With
       
        End If
       
    End Sub

    Wednesday, August 2, 2017 12:44 PM
  • And now that I think about it, what I would really want would be to just hide columns B through D if D is 0, not the entire row.  Don't know if that's possible but in a perfect world that's what I would be looking for.  Thanks for trying to help with this. 
    Wednesday, August 2, 2017 12:50 PM
  • I cannot reproduce your problem with the information provided.

    If I put values in K14,K10,B5 and C5 to produce a positive result (and I filled the range with your formula), that row is unhidden, and all the others are hidden.  If it produces a negative result, all are hidden.

    Without a copy of your workbook that demonstrates the problem, I have nothing else to offer.  If you want to share the workbook, post a copy (with sensitive information removed) on a public sharing site, such as OneDrive, and post the link here.


    Ron

    Wednesday, August 2, 2017 7:39 PM
  • Ron,

    Yes, actually I was able to reproduce it as well in a blank spreadsheet, using the same formulas as my current spreadsheet.  The only problem I came across was a "Run-time error '9':  Subscript out of range" error, but when I click on End it actually hides the rows with zero values in all worksheets.

    So is the problem the way I have it combined with the other macro?  They were both in Module3 as shown above.  I apologize that my knowledge of VBA is relatively limited.  I've been working on this spreadsheet a long time, and this is all I need to get across the finish line.  I appreciate any help you can provide. This is for work so I don't know that saving the file on a public sharing site is appropriate, even with sensitive info removed.

    But now that I have tested using my exact code and formula, it seems like there has to be something with the "logistics" of it as opposed to the actual code as I had thought.  Thanks for any additional help you can possibly provide.

    Ron

    Wednesday, August 2, 2017 8:20 PM
  • OK, just to prove how uninformed I am, the reason it was not "working" is that I was trying to run the whole macro using a From Control Button.  That button was originally assigned to the Export to PDF macro, and I added the HideRows macro in after the fact. I thought, because it appeared that they were all "saved" together, that it would just run automatically. I created a second button to specifically hide rows, it works but is generating errors:

    Run-time error '13': Type Mismatch and when I try to Debug it highlights the "If Not r > 0 Then" 

    I guess that is my problem now.  Glad you are chipping away at this for me.  I really appreciate it again.

    So if you have insight into that error, great, but again it does seem to hide the rows as intended.  And any advice on options to only hide a range of cells as opposed to the entire row?

    Wednesday, August 2, 2017 8:48 PM
  • Be sure you have `Option Explicit` at the beginning of the module.  This will help you catch typos and undeclared variables.

    Check the value of `r` at the time of the error:  address and value

    If that doesn't help, provide a workbook that demo's the error.

    AFAIK, you cannot hide just a few columns of a row.  (and I have a problem envisioning what that would look like)


    Ron

    Wednesday, August 2, 2017 9:09 PM
  • I figured it out...finally.  Kind of dumb after I thought about it.  Some of the worksheets have information that is not pertinent unless there are values in each of the eight worksheets (P20-P27).  So if there are no values they are not included in the final pdf version.  But because they had #VALUE!'s in column D in those unused worksheets I was getting the run-time error.  That's why it seemed to be working but still getting an error message.  It was working for those worksheets that had data but bombed out when it encountered the #VALUE!'s.

    OK, last questions I promise.  The reason I was just trying to hide a few columns of a row was that I have a chart to the right of column D that gets skewed as I hide the entire row.  So while the spacing looks great at first, if I hide several rows, the chart shrinks and looks bad.  Is there a script that I could use to delete those unused cells and move everything below them up?  Similar to the delete cells command.  I understand that I will need to back this up before testing and that there is no undo feature for this.  But if I have a template and run from the template each time and don't save it, is there a way to do that?

    Second, and this is minor, but do I need two buttons to run both macros or can they be combined into one somehow?  Thanks again, I thought I knew so much about Excel until I started getting into VBA, now I feel like I have no idea what I'm doing....totally different.

    Thursday, August 3, 2017 12:37 PM
  • Glad you figured it out.

    So far as your other questions:

    • Check the VBA MSDN Reference for the Cells.Delete method.  You'll see there is a shift argument
    • To run both macros from one button, you can
    •     Have button point to first macro to run
    •     Have first macro to run call the second
    • OR
    •     Have button point to a third macro
    •          Have the third macro call the others in order

    The second option is more flexible as you can add more macros, if necessary, and also it is more clear what you are doing.


    Ron

    Thursday, August 3, 2017 1:01 PM
  • Hi Ron, hope you can help and apologies if the answer is in the thread. I have a table that is populated via IFS. It looks up the year and gives me the data I need based on that year. My issue (though it is cosmetic) is that I will have a different number of suppliers each year and I would like this table to grow and contract based on that. I've tried your hide rows Macro as above and changed the formulae as follows

    Option Explicit
     Private Sub Worksheet_Activate()
         Dim r As Range, c As Range
     Set r = Range("f7:g26")
     Application.ScreenUpdating = False
     For Each c In r.Rows
         If Len(c.Cells(1).Text) + Len(c.Cells(2).Text) = 0 Then
             c.EntireRow.Hidden = True
         Else
             c.EntireRow.Hidden = False
         End If
     Next c
     Application.ScreenUpdating = True
     End Sub

    I did see your suggestion that worksheet_change event macro may be needed but the thread got busy and I couldn't see how to add this or if it was relevant to my issue.

    Friday, December 8, 2017 9:58 AM
  • I don't see where you indicated the problem with your modification?  What isn't working?

    I confess I don't understand from your modification exactly which cells you are trying to check for non-entry before hiding a row. Your code seems to check for two contiguous rows in the table. 

    Perhaps if you wrote out in words what you are trying to do, I might be able to provide some guidance.


    Ron

    Saturday, December 9, 2017 7:06 PM
  • I am trying to autohide some columns & rows which remains empty during the use of Excel WorkBook. Following is the Macro I am trying please guide me how to make it better

    Option Explicit
     Private Sub Worksheet_Activate()
         Dim r As Range, c As Range
     Set r = Range("a8:b100")
     Application.ScreenUpdating = False
     For Each c In r.Rows
         If Len(c.Cells(1).Text) + Len(c.Cells(2).Text) = 0 Then
             c.EntireRow.Hidden = True
         Else
             c.EntireRow.Hidden = False
         End If
     Next c
     Application.ScreenUpdating = True
     End Sub


    Private Sub hideEmptyColumns()
         Dim r As Range, c As Range
     Set r = Range("ae4:ag5")
     Application.ScreenUpdating = False
     For Each c In r.Columns
         If Len(c.Cells(1).Text) + Len(c.Cells(2).Text) = 0 Then
             c.EntireColumn.Hidden = True
         Else
             c.EntireColumn.Hidden = False
         End If
     Next c
     Application.ScreenUpdating = True
     End Sub

    Tuesday, August 14, 2018 11:42 AM
  • What is the problem you are having with your code?

    Ron

    Tuesday, August 14, 2018 12:37 PM