Free Microsoft Excel 2013 Quick Reference

Formula to hide blank rows

I have a nine page order form - my question is if it is possible to hide a
row if the item in that row is not chosen. I want to end up with a two or
three page order form showing customer choices instead of nine pages of half
filled in rows - any suggestions? Thanks.

Post your answer or comment

comments powered by Disqus
Excel 2003.

Is there a formula to hide or display a row/column based on the value of
another cell?

I have a worksheet which has data pulled through of various lenghts. I know
how to hide all blank rows so i can reduce the print range, but on this sheet
the data is in every third row down. How can I hide blank rows after the last
entry, but not hide the blank rows between the data?

Hi everybody,

I use the following code to hide blank rows in the active sheet,,

I want it to work with all sheets,

Can you help me in this case,

Sub Hide()

For A = 1 To 1000
BK = ActiveSheet.Cells(A, 1)
If BK = "" Then
Rows(A).Hidden = True
End If

End Sub
Thank you,


I have this code (compliments of VBA Noob) which hides all blank rows within a range ("Range1") P16:V650
It works great in a new worksheet with little amount data, however within my heavy worksheet, it takes over a minute to compile.

Sub HideRows()
Dim Rng As Range
Set Rng = Range("Range1")
For i = Rng.Rows.Count To 1 Step -1 
If WorksheetFunction.CountA(Rng.Rows(i)) = 0 Then
Rng.Rows(i).EntireRow.Hidden = True
End If
Next i
End Sub

I'm currently using this secondary code (provided by shg) which hides all blank rows between two columns, (Range1) & (Range2)

Sub JP() 
     Intersect( Range("Range1").SpecialCells(xlCellTypeBlanks).EntireRow, _ 
    Range("Range2").SpecialCells(xlCellTypeBlanks).EntireRow) _ 
    .EntireRow.Hidden = True 
End Sub

Works terrifically in my worksheet, and Is lightning fast.

Is there a way to modify the 1st code for more speed efficiency?
My initial inquiry was to hide blank rows within a Range

thanks for any help.



I have 4 pivot table filters and I have set up the rest of a dashboard using the cube formulas. This is all working great. Finally, when the user changes a pivot field filter, the CUBERANKEDMEMBERS are ordered on a tuple such that sometimes the values are blank.

I have a macro to hide/show these blank rows - but what I want to do is call the hide/show macro when a filter changes and the new values has been retrieved from the cube. I have tried using various combination of Worksheet_Change and Worksheet_Calculate but the results are proving to be reliable. The result will then be almost like a pivot table but much more flexible. Any help is much appreciates.

Thanks an advance for your time - I am using excel 2010 for this BTW.

Hi there,

I am looking for a macro to detect and hide blank rows.
I am using two tables in my worksheet.
The second table is only showing a value when the answer on
the if command in that table is positive.
However, I want the results in my second table nicely sorted out
without any blank lines between them.
What would be the best way to hide the empty lines?

Thanks in advance,



I want a bit of code to hide the 'blank' row of information in a pivot, can anyone help.

Kind Regards,


Each Row In My Worksheet Is A Part Number, With Columns For Description,
Quantity, Cost, Etc. I Am Trying To Figure Out How To Create A Formula
That Would Tell It To Hide All Rows With A Quantity Of "0". Please Help

View this thread:

Hi All

Is it possible to write some code that finds the blank rows in a set range in column A then hides them?

i.e cells A14:A34 are blank and need hiding but cells A1:A13 and A35 are not.

Thanks in advance


I have a great code that I want to modify. The code hides rows if the sum of the row is smaller or larger than zero. But I want to modify the code so it hides the rows only if it is blank. So a row with a string of text in it will not get hidden.

Sub Komprimera()
    Dim HiddenRow&, RowRange As Range, RowRangeValue&
     '< Set the 1st & last rows to be hidden >
    Const FirstRow As Long = 4
    Const LastRow As Long = 65
     '< Set the columns that may contain data >
    Const FirstCol As String = "C"
    Const LastCol As String = "AZ"
    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
             'there's nothing in this row yet - hide it
            Rows(HiddenRow).EntireRow.Hidden = True
        End If
    Next HiddenRow
    Application.ScreenUpdating = True
End Sub

Good day, need help on macro.
I'm trying to find a way to delete blank rows that contains formula.
I have two excel sheets.
sheet1 contains information and sheet2 references the values from sheet1. Let's say I put the following values in sheet1:

After entering above values in sheet1, here's what sheet2 would look like:
(blank that holds formula) =IF(ISBLANK(Sheet1!A2)," ",Sheet1!A2)
(blank that holds formula) =IF(ISBLANK(Sheet1!A3)," ",Sheet1!A3)
(blank that holds formula) =IF(ISBLANK(Sheet1!A5)," ",Sheet1!A5)

What I really wanted to happen is for macro to eliminate blank rows in sheet2 to look something like:

Is it something possible?
Hope someone can help me.

I have the following formula in cell A5 and throughout the sheet
=if(Sum(A1:A4)>0,Sum(A1:A4),"") If the answer returns "" (blank) I want my
macro to hide the row using
"Range("MyRange").Columns(5).SpecialCells(xlBlanks).EntireRow.Hidden = True".
However the code sees the formula and says it is not blank. How can I get
around that?

Each Row In My Worksheet Is A Part Number, With Columns For Description, Quantity, Cost, Etc. I Am Trying To Figure Out How To Create A Formula That Would Tell It To Hide All Rows With A Quantity Of "0". Please Help


I have a spreadsheet that has cell references to another spreadsheet.
I am trying to write a macro script that when the document is opened (and refreshes data from the linked spreadsheet) it looks at the rows which are blank and hides these rows.

So far I have found the following code below which works great, however this macro re-runs everytime you navigate back to the worksheet, causing the screen to flicker for a few seconds.

Private Sub Worksheet_Activate()
Dim rng As Range, cell As Range
Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("B6:G120"))
For Each cell In rng
If Application.CountA(cell.EntireRow) = 0 Then cell.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub
I just want the script to run once.

I have tried replacing the top line of script with:

However I was then getting a debug error associated with line 3.

Can anybody offer any help?

Thanks in advance!


is there a formula to remove all blank rows in a sheet all at once

Hi everybody,

This is the first time I have posted something on a forum, but I cannot seem to find the solution to my problem no matter where I look.

I need a macro for a button that will hide blank cells in a named range. The problem is that the named range has a header row, I want the macro to hide ALL rows including header if the rows below the header are blank, but only hide blank rows if and leave header row if there is information entered.

This would be easier if I didn't have the header to worry about, I could just do a macro to hide all blanks. But it would not hide the header, I DO want it to hide the header though if the rest of the rows are blank.

Any help would be greatly appretiated. Thank you all and have a great day.


How do I go about writing VBA code to do the following:

1. Select a particular range of cells in column A
2. If one of these cells (in column A) returns a FALSE response to the Excel IsNonText formula to hide its entire row.

The circumstance is a little difficult to explain but let me know if you need clarification and I'll do my best.

Thanks very much for your help,


Hide Multiple rows.

I have text within two columns A, D
How can I Hide Blank rows which spans two column Named Ranges, "Range1" (A1:A15) "Range2" (D1:D15)

As an example:

Beginning with this:



To this , after hiding blanks:



Thanks for any help


Okay I have a spreadsheet that is linked to another spreadsheet in a workbook. The information comes from an export of an access query into a template in excel that I am using just to store the values, then I link the values to the appropriate field in another sheet. I was wondering is there a way to programmatically hide blank rows in this sheet starting at a specific row of the page.

I'm trying to write a Macro that will hide columns based on a user input, and then rows based on what is left over.

Basically, when the workbook is opened, I want a box to pop up that says enter store number. Then the store number is typed, and OK is pressed, and it will hide all columns in the range Z:IV that do not have that store number in row 1. There will only and always be one column that will remain unhidden.

After that is done, I want to hide all rows in the range 2:2000 that have a blank or 0 value in that column that was NOT hidden in the above step. This column will be dynamic though.

IE: If you enter store "101" in, all columns right of Y will hide except column AA (just an example), which contains "101" in cell AA2. Then all rows that are blank or have a value of 0 in column AA will be hidden.

Then if you enter store "202" in, all columns right of column Y will hide except column BA this time, which contains "202" in cell BA2. Then all rows that are blank or have a value of 0 in column BA will be hidden as well.

I would like this to all happen in one macro. So when the store # is entered, everything is done and ready to view.

I know this is probably difficult, but if it makes it much easier, I guess I could deal with deleting rows and columns instead of hiding. I would much rather hide, but if deleting is the only way, it is acceptable.

Thanks in advance.

Dear Friends,
Suppose i have a worksheet named B, where I have a table consisting of 35 rows and 7 columns. The rows record the data of each years. like row 1 for year 1, row 2 for year 2 and so on. In the columns, are recorded data corresponding to the particular years. These data get automatically get filled as cell references from another worksheet say Worksheet A. I have a cell (B7) in Worksheet A, where I put the value for the number of years whose results I want , and in Worksheet B, I get data automatically filled up in the table for that number of years, leaving blank rows and columns, below that number of years.

My question is : is there any way whereby in Sheet B I would get exactly that number of rows whose value I input in cell B7 in sheet A. ie. if i put say 10, the table would shrink only to 10 rows... or maybe to 15 rows, if i input 15 in cell B7 in worksheet A.

I want it to be automatically executed rather than using manual "autofilter" options ???

Any helps and suggestions ......

thanx in advance


I'm new at VBA and have a question. I have data being dumped into a spreadsheet and another spreadsheet with formulas that relate to this data. An example of the formula in every cell is: =SUMIF(Data!$A:$A,REPORT!A9&REPORT!$C$6&"-"&REPORT!$B$7,Data!$F:$F). We are talking about 500 rows.

I want to hide a row if I don't have overtime (OT) hours that day. Meaning that if I don't have OT on neither shift, I don't want to see that row. This is going to change daily, so, I have all the different operation numbers in that report, but need to hide the rows if I don't have OT hours. I thought about doing an IF-then-else statement, where If columns a,b and c are not numerical values, then hide active row else, show row or do nothing. I don't know the syntax to write that.

Any help would be greatly appreciated.


I have a range of data with about 200 rows. Usually a chunk of about 20 rows
towards the bottom is blank. I would like to hide those rows automatically
and if they do suddenly have values to automatically unhide them. Is their a
way to do this?



How do I write a macro that will hide rows that contain all zeroes in a
certain field. For example, if columns B:N are blank for rows 5-8 contain
only zeroes, how do I tell the macro to hide these rows IF those columns are
blank? Preferrably I'd like this to work for the whole worksheet. Thanks!

No luck finding an answer? You could always try Google.