Hiding Rows Based on a Cell Value

by Allen Wyatt
(last updated February 10, 2014)

66

Excel provides conditional formatting which allows you to change the color and other attributes of a cell based on the content of the cell. There is no way, unfortunately, to easily hide rows based on the value of a particular cell in a row. You can, however, achieve the same effect by using a macro to analyze the cell and adjust row height accordingly. The following macro will examine a particular cell in the first 100 rows of a worksheet, and then hide the row if the value in the cell is less than 5.

Sub HideRows()
    BeginRow = 1
    EndRow = 100
    ChkCol = 3

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

You can modify the macro so that it checks a different beginning row, ending row, and column by simply changing the first three variables set in the macro. You can also easily change the value that is checked for within the For ... Next loop.

You should note that this macro doesn't unhide any rows, it simply hides them. If you are checking the contents of a cell that can change, you may want to modify the macro a bit so that it will either hide or unhide a row, as necessary. The following variation will do the trick:

Sub HURows()
    BeginRow = 1
    EndRow = 100
    ChkCol = 3

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value < 5 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1940) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Displaying the User Name in the Status Bar or Title Bar

Sometimes it can come in handy to know who the current computer user is, as far as Word is concerned. This tip presents ways ...

Discover More

Switching Editing Location

Excel allows you to edit the contents of a cell in two places—the cell itself or in the Formula bar. If you want to ...

Discover More

Hiding Formatting Changes in Track Changes

Word can easily (and handily) keep track of changes you make in your document. You may not want all your changes tracked, ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (MENU)

Conditionally Formatting an Entire Row

Need to conditionally highlight an entire row based on the contents of a single cell in each row? This tip explains how you ...

Discover More

Displaying Latitude and Longitude

If you work with geographic data, you may need a way to display latitude and longitude in a worksheet. This tip examines ...

Discover More

Replacing Cell Formats

Need to replace the formats applied to some cells with a different format? Those using Excel 2003 will find it easy; those ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight more than 7?

2017-03-18 20:34:48

Alan Elston

Hi G S PANDI
I do not think it is too difficult to give you a code to do something like you want.
Codes that start automatically are known as Event Codes or similar.
If you do a Google search using variations of this_..

VBA Event code hide rows on cell value change

_.. then you will get loads of answers.

The codes have to go in a Worksheet code module rather than a Normal code module.

But if you “....don't know anything about VBA codes...” then I am not sure if it will be much help giving you a code that you would not know what to do with !!

It would be difficult to then help you further in the confines of the Comment Platform here.

I suggest you try to formulate a more detailed question and request for help at an Excel Forum.
Just now, for example , I started answering a similar question here:_...
https://www.excelforum.com/excel-programming-vba-macros/1177751-vba-for-outline-based-on-the-column-values.html#post4608769
_.. take a look and see if anything there could help get you started.
Alan


2017-03-17 22:35:43

G S PANDI

Hi there,
Can anyone help me. I don't know anything about VBA codes. However, I managed to create a data sheet with various formulas.
My data sheet contains 500 rows and 15 columns. But many of them may not be applicable many times. Hence I want to hide those items which are not applicable.
I will add a formula in Column K1:K500 to give value 0 or 1. I want the excel to hide automatically the rows with the value 0 in K. And when the value becomes 1, it should be auto unhidden. Can any one help please. Thanks in advance.


2017-01-18 12:01:44

Anthony

Hi,

I am not familiar with Macros but I'm hoping someone can help me. I do scheduling and I want to have a Macro or a Formula saying if someone is off to hide that row. We have a weekly schedule that we break down into a daily schedule. I want to be able to run this Macro or Formula on the weekly schedule to produce our daily schedule. I have tried typing some of these examples in the comments but I keep getting errors. Any help would be appreciated. Thanks


2017-01-05 19:34:37

Steve

Hi,

How do I create the macro to hide cells between a range (i.e. greater than -5 but less than 5?)


2016-11-02 17:08:21

Ron

Hi,
I have created an attendance program and I am looking for help with active and inactive employees. I have 13 tabs (12 for each month of the year) the 13th would be a total reconcile of all employees.
What I’m trying to do is have one cell on the totals tab with a Data Validation of Yes or No, look at the other 12 tabs and hide if the employee becomes inactive.
I am not savvy at all with VBA
Thanks for your help.
Ron


2016-10-27 08:39:56

TROY

Or another way I could do it, is maybe with Hlookup so it only pulls the information from sheet 1 to sheet 2 if the cell on sheet one has a figure other than 0, again I'm not sure how to type that rule into the cell. so if anyone could give me some pointers on that too, I would really appreciate it. forgive my ignorance, I am extremely new at excel.


2016-10-26 12:31:13

TROY

I need to have each row hidden when the C column of that rows value is zero, for example

when C5 is 0 row 5 is hidden, I need to do this for 30 rows per sheet. I also need it to be not hidden when it has a significant figure. can anyone tell me how to write the VBA for that please


2016-10-18 20:09:28

Ryan

Joe, try this

Sub HideRows()
BeginRow = 10
EndRow = 26
ChkCol = 5

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 1 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
BeginRow = 31
EndRow = 39
ChkCol = 5

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 1 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
BeginRow = 44
EndRow = 47
ChkCol = 5

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

You can adjust the macro to suit the cells that you need it for and change accordingly.
Hope this helps.


2016-10-18 18:17:13

Joe

Can anyone let me know if there is a macro that will hide rows that have zero value but IF it does have a value, can I not hide the row above it as well? The current spreadsheet I am working on has template headers that may or may not be used. For eg:

Cells B6 & C6 will read:

03A Concrete Formwork/Flatwork

and the row below it will have a value/description in D6.

I have a macro that will delete any row that doesn't have a value in the "D" column but if it does I want the "Column Header" row to stay.

Sorry if that doesn't make a whole lot of sense. It would be easier if I could attach a screen shot.


2016-10-15 06:14:20

Willy Vanhaelen

@michelle

Perhaps you can find inspiration to solve your problem in my comment of 26 Feb 2016 which is about a very similar case.


2016-10-14 13:29:32

michelle

I have a PO that I want to automate for the customer.

My sheet contains multiple parts for multiple retailers.

If customer wants to view only the parts available to them they select from the dropdown - this should trigger all the other parts to "hide".

I currently have 5 options on the retailer drop down.

If XXX then hide 12:80,
If YYY then hide 81:120
If ZZZ then hide 121:150


Can you please help I am not VBAA savvy! but can follow your lead



2016-10-05 10:14:49

Rima

Hi Guys,
I have a loan amortisation schedule with maximum tenor 360 months. I want to add a macro that if the loan tenor in cell B2 which currently displays say 240, all rows between month 241 and 360 in the schedule is hidden. How do i proceed?


2016-09-22 05:57:30

Michael (Micky) Avidan

@Sarthak Bhargava,
Try:
Sub HideColumns()
Application.ScreenUpdating = 0
For Each CL In Range("A17:KC17")
If CL = 0 Then
CL.EntireColumn.Hidden = 1
Else
CL.EntireColumn.Hidden = 0
End If
Next
Application.ScreenUpdating = 1
End Sub
----------
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL


2016-09-22 03:44:28

Sarthak Bhargava

Sub HURows()
BeginRow = 1
EndRow = 100
ChkCol = 3

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 5 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub




i need a similar thing for hiding columns from A to KC by checking the value of row 17 in each column.
if it is zero then hide that particular column or else unhide it automatically.


2016-09-14 07:01:00

ashton

Hi there, how can i use this with an AND statement, I.E if two cells each = 0 then hide the row?


2016-08-26 05:55:17

Michael (Micky) Avidan

@achul,
Check out the function: SUBTOTAL and its arguments.
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL


2016-08-26 04:18:07

achul

how to make hidden row and show row as a fungtion like 'SUM','AVERAGE' and other...?


2016-08-08 01:14:32

Brad

Thanks Willy that got it. Shows you how new I am to this.

I was renaming "Sub Feedscope()" not realising "Private Sub..." was also a macro name.

A rookie mistake!


2016-08-05 05:47:59

Willy Vanhaelen

@Brad
You cannot have two macros with the same name. You can try to continue in your macro with the other section:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("c6").Value = "Yes" Then
Rows("7:13").EntireRow.Hidden = False
ElseIf Range("c6").Value = "No" Then
Rows("7:13").EntireRow.Hidden = True
End If
If Range("c14") = "Yes" Then
Rows("15:21").EntireRow.Hidden = False
ElseIf Range("c14").Value = "No" Then
Rows("15:21").EntireRow.Hidden = True
End If
End Sub


2016-08-05 02:38:22

Brad

I've tried various codes for this such as this:
Sub Feedscope()
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("c6").Value = "Yes" Then
Rows("7:13").EntireRow.Hidden = False
ElseIf Range("c6").Value = "No" Then
Rows("7:13").EntireRow.Hidden = True
End If
End Sub

It works just fine if there is only one copy of the code but I want to have this same code for multiple sections on one worksheet. So I have copied the code and changed C6 to C14 and Rows7:13 to 15:21.

However the second code creates an error message "Ambiguous name detected:Worksheet_Change" and neither code works.

I am pretty new to VBA so really appreciate your suggestions.


2016-06-28 22:06:32

Nabil

Hello,

I would like to have your kind help to find me a VBA to hide the rows with value ZERO in different ranges of columns based on choosing the name from dropdown list. Below is my chart


A BCD EFG HIJ (columns)
----------------------------------------
YEAR WEST CENTER EAST
---------------------------------------
2012 347 382 000
2013 000 284 273
2014 943 000 000
2015 000 743 732
2016 269 000 527

when I choose WEST from the dropdown list and then I click hide zeros button, the chart should be like

A BCD
------------
YEAR WEST
------------
2012 347
2014 943
2016 269

then, I like same work to be applied on CENTER and EAST, so when I choose CENTER and hide the zero, the chart should be like:

A EFG (columns)
----------------------------------------
YEAR CENTER
---------------------------------------
2012 382
2013 284
2015 743


Is that clear enough and possible to find me a solution ?

Millions of thanks in advance.

Nabil



2016-06-21 11:24:45

Nicole B

This is great. Is there a way to run a reverse macro to get the cells back when you do need to see them?


2016-06-10 10:29:55

Brandon

I have been using this code for what I've been doing, but I need a slightly altered code for the tests we run. If anyone could help, that would be great.

Basically, column B is decimal time in minutes and between rows 237 and 9768, the difference in time between a cell and the cell above it is either 10 seconds (give or take a second) or the difference is 1 second(give or take a second). I need it to go through and hide entire row if the difference in time between one cell and the cell above it is less than 5 seconds. For example, value of B300 is 111.70 and B301 is 111.72. Since the difference in time is 1 second, or 0.02 minutes, it would be hidden.

I had some help from a smart guy on Reddit, however there were some misunderstandings and he couldn't figure things out. Here is the link: https://www.reddit.com/r/excel/comments/4mucb4/i_have_a_test_that_takes_data_in_excel_i_need_to/

That might help explain things more. He shows code and provide examples of a spreadsheet as well as pictures to explain what I need.

His code successfully starts hiding rows at the correct place and stops hiding rows at the correct place, but in between, instead of hiding all rows, it only hides a few and leaves some unhidden as specified by the "timeinterval" in the code that he gave.

I'd really appreciate any help!


2016-05-04 15:21:30

Brent

Is there a way to do this for multiple ranges on the sheet? Instead of the code checking rows 1-100 every time, I want to set it to check rows 2-3, 35-39, and 78-79.

The bigger the range, the more it slows down my sheet.


2016-05-02 21:33:10

RP

THANK YOU for this solution. I've been searching high and low how to conditionally hide rows without much help. This worked perfectly!


2016-03-13 09:30:48

Lee S.

I have a 2 year construction schedule on excel 2007 & 2010 that has sub’s responsibilities in as many as 200+ rows and dates are shown in columns. I use conditional formatting to highlight dates and progress of each sub. I have frozen panes at cell I-10 to maintain sub’s activities on the left and dates across the top.
I can enter a date in a cell that uses the period select function to highlight the proposed start of a 3 week look-ahead schedule.

My 1st issue is when I enter the date to begin a 3 week schedule; it will highlight the proper beginning date column as programmed, but I have to manually scroll over to the highlighted column to view (potentially 500+ columns to the right). I want Excel to automatically bring highlighted column to the left; (within viewing area) beside column ‘H’.

My 2nd issue is that I want to be able to print just the 3 week schedule and frozen panes starting on the highlighted column (basically print the same thing I see on the screen) and down to the bottom of sub’s responsibility rows (this could be as few as 10 and as many as 200 rows).
I have been able to use [Ctrl +P] to open printer formatting and using the “print Selection” option as a elementary work around however I have been unsuccessful accomplishing the dynamic print issue with the OFFSET function. I may not have the correct format or function, or is this possible to accomplish thru VBA?
Thanks!


2016-03-11 07:50:07

Robert H

Ha, apologies for my earlier comment. It was just a big database and it eventually finished.. No need to comment.. all good here. Thanks


2016-03-11 07:48:45

Robert H

Hi All, I've tried this code, except switching to ChkCol = 19 as this was the column I placed values of either 0 or 5, depending on which rows I would like visible. However it loops continuously and it doesn't stop?


2016-03-03 11:32:00

Willy Vanhaelen

@ashokkumar

I am glad it works as expected.

For the multiple range:

Case "XXXX": Union(Range("11:19"), Range("41:49")).EntireRow.Hidden = True

You can add as many ranges as needed.


2016-03-03 00:31:51

ashokkumar

Thank you Willy Vanhaelen!

It works fine!

Additionally i need to know how to hide multiple range of rows (11:19 & 41:49)


2016-03-01 11:19:45

Sjaak

Awesome, thanks a lot!


2016-02-26 11:22:52

Willy Vanhaelen

@ashokkumar

This macro will do the job:

Sub HideRows()
With ActiveSheet
.Cells.EntireRow.Hidden = False
Select Case .Range("A1")
Case "XXXX": .Range("11:19").EntireRow.Hidden = True
Case "YYYY": .Range("21:29").EntireRow.Hidden = True
Case "ZZZZ": .Range("31:39").EntireRow.Hidden = True
End Select
End With
End Sub

You can even automate the job. Put the following macro in the worksheet's code page. To get there right click the sheet's tab and select "View Code" .

When you enter XXXX, YYYY, or ZZZZ in A1, the corresponding rows will automatically be hidden. When you clear cell A1, all rows are unhidden.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("A1").Address Then Exit Sub
Cells.EntireRow.Hidden = False
Select Case Range("A1")
Case "XXXX": Range("11:19").EntireRow.Hidden = True
Case "YYYY": Range("21:29").EntireRow.Hidden = True
Case "ZZZZ": Range("31:39").EntireRow.Hidden = True
End Select
End Sub


2016-02-25 01:48:15

ashokkumar

I need to hide some rows depends on the A1 text. In my worksheet, when A1 is XXXX then hide (11:19) rows and in the same A1 has YYYY then hide (21:29)rows and in the same A1 has ZZZZ then hide (31:39). please advice how to proceed.


2016-01-19 07:40:17

Richard

I want to hide rows that contain a Y in the AB column. The same formula with Y versus a number does not seem to work.


2015-11-29 17:46:37

Natalia

Thanks so much. I changed it to hide rows in a particular area of my worksheet where a field was empty.
So easy to use!!!
Fab!


2015-10-16 02:49:38

Alan E

Fantastic bid of code. I googled my problem and ventured into this site as a result. Tested the macro and it works a treat. Brilliant !! I look forward to coming to this site for more tips. Thanks so much.


2015-10-07 11:13:30

Jack

How do I hide a row if and only if both columns contain the same number? If column 4 is not equal to column 3, I do not want any of the row to hide.


2015-10-05 11:49:23

Alistair Mickleburgh

I need help on how to use this with a command button


2015-10-01 17:24:00

David Foran

Sweet! Thank you Allen Wyatt


2015-08-25 13:32:36

Carina

How do I use this code with a command button? Also need the cell to = "COM" instead of being < 5

Thanks!!!


2015-08-20 09:41:30

Brian

The hide rows works for me but I have a sales order form that I would like to hide the header row for a particular section if there is nothing being purchased from that section. Otherwise I want to leave the header. Example:
Shirts (header)
Short sleeve Value 0
Long Sleeve Value 1

So I want the macro to hide the row with the value zero but not hide the header. I can do this with a range that excludes the header but there are cases like this:

Shirts (header)
Short sleeve Value 0
Long Sleeve Value 0

Where I want the header and the rows, since they are 0 to be hidden.

Any ideas are greatly appreciated!!


2015-07-20 18:35:02

Dean Lorimer

I have a workbook with 2 active worksheets. I would like to keep a running list of all people who have served in this post-11 serve at a time. I have put an end date when they finish serving and would like to have the row hide when the end date is entered. I have entered this code:
Sub HideDeathDate()
'
' HideDeathDate Macro
' This macro will hide after death date entered
'

'
BeginRow = 1
EndRow = 400
ChkCol = H

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = Null Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub Application.Goto Reference:="HideDeathDate"

Application.Goto Reference:="HideDeathDate"


2015-06-10 11:31:06

Willy Vanhaelen

@Deane

This macro will do the job:

Sub HURows()
BeginRow = 1
EndRow = 200
BeginCol = 3
EndCol = 6
For rowcnt = BeginRow To EndRow
If Application.CountIf(Range(Cells(rowcnt, BeginCol), Cells(rowcnt, EndCol)), "=0") Then
Rows(rowcnt).EntireRow.Hidden = True
Else
Rows(rowcnt).EntireRow.Hidden = False
End If
Next rowcnt
End Sub

Note that the zero value must be a 0 or a formula returning 0.
An empty cell is not considered to be 0 by CountIf.


2015-06-09 11:17:41

Damian

Thanks Allen,

I took it a step further and embedded this in a Private Sub for the sheet I was working in where a cell would alter the values in the sheet and if there were any zero values to hide the rows. Then if the cell changed again, unhide all rows and re-evaluate the rows to hide based on the new data.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("D5:D5")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

BeginRow = 7
EndRow = 27
ChkCol = 5

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End If
End Sub


2015-06-05 05:58:07

Deane

Hi there I have used the code supplied above to hide rows based on zero values but it only considers the 3rd column. How do i get the code to consider a range of columns ando only hide the row if there are zeros values in the entire range?

Code I used:

Sub HURows()
BeginRow = 1
EndRow = 200
ChkCol = 3

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub


2015-05-07 14:48:06

Greg

Howdy,

My data resides in a table from rows 71-250. Column "A" has values "1" through "180" respectively. I'd like to all hide rows, within 71 through 250, where the values are > the value of cell "C6" (EXAMPLE: cell value is "60"). I can do it for one-off individual rows but would rather not repeat the code 250 times. Is there an easier method?

Many thanks in advance.


2015-05-01 08:12:45

christina

I need to know how to do the very 1st macro for an entire workbook.

Thanks!


2015-01-26 11:26:49

Julian

Hi,

Allen, thanks so much for your code, slightly modified as below.

My need is to hide the row below in a block or 'range' of cells. There are 50 such ranges.

Each range has 8 consecutive rows - eg. 12 to 19 - repeating every 10. Every 'zero' row and every 'one' row must be visible.

How can the code be made to apply to the user's currently selected range, which could be any block of 8 (ie D12 to D19, D22 to 29, D32 to 39 etc etc), without writing out 50 times?

Changing beginrow and endrow values hides all the lowewr rows, and takes ages to complete.

Thanks in advance.


Private Sub Worksheet_Change(ByVal Target As Range)
BeginRow = 10
EndRow = 18
ChkCol = 4
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "" Then
Cells(RowCnt + 1, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt + 1, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub


2015-01-20 17:22:08

Nina

How to a add a range into the code? Instead of <5 I need it to hide rows that are outside of a range, defined in 2 cells.


2014-12-11 09:45:23

Dave

Terry asked a simple question: "My problem is I have no idea how to add this to my spread sheet. Can you give me a ste by step on how to add a macro?"

I'm a novice @ VBA only half a step ahead of him (or her!).

I just do TOOLS/MACRO/RECORD NEW MACRO/OK. That would start writing VBA code for any keysrokes that follow if I did anything. But, I do nothing but TOOLS/MACRO/STOP RECORDING. Now, go to TOOLS/MACRO & Macro1 or whatever Excel named it. EDIT and you can then PASTE code you've CUT or enter VBA code by hand.


2014-12-10 16:25:42

Gerardo

I need to run this Macro but not hid blank cells

Sub HideRows()
BeginRow = 9
EndRow = 155
ChkCol = 3

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value < 500 Then

Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt

End Sub


2014-12-03 02:04:01

Jonathan

Good day Gents,

I utilized the code, I need it to however not hide the "Blank" Cells only Cells containing the numeric value 0.

Please advise?

Kind Regards
J


2014-09-27 12:47:54

Willy Vanhaelen

None of the variables in this tip's macros are declared which is bad practice.

Instead of defining the start row, end row and check column in the macro you can better simply select the rows in the check column and run this tiny macro:

Sub HURows()
Dim cell As Range
For Each cell In Selection
'change <> 4 to suit you need, i.e. < 5 as in this tip
cell.EntireRow.Hidden = cell <> 4
Next cell
End Sub

@Brad, you can use this macro as is.


2014-09-26 15:24:21

Brad

Hi,

I have a workbook with three worksheets - Assessment, Results and Management Response. I would like particualr rows to be hidden on the Results and Management Response worksheets if the value in a cell on the Assessment sheet does not equal 4.

I would like the formula to check for values not equal to 4 on the Assessment sheet. For example, if G15 on the Assessment sheet is 3, I would like to hide rows 23 on the Results sheet and 23 - 28 on Management response.

So far, I have scoured the web but to no avail. Can anyone please help me?


2014-09-10 11:26:32

Terry

The macro up top should do just what I need. My problem is I have no idea how to add this to my spread sheet. Can you give me a ste by step on how to add a macro?

Thanks


2014-06-18 15:18:09

Bob

To answer my own question a formula invoked from a spreadsheet cell is not permitted to alter any other cell including the hidden status.

Apparently there are workarounds but I haven't tried them yet because it isn't trivial http://stackoverflow.com/questions/24220347/unable-to-hide-row-excel-2003-from-function-invoked-from-formula


2014-06-13 11:49:33

Bob

This code work perfectly for me as a Sub or Function when invoked as from a command button, but it does nothing when invoked as function in a formula.

What am I doing wrong?
'works as button
Private Sub CommandButton3_Click()
x = HRows("A1")
End Sub

=HRows("A1") in spreadsheet doesnt work

Public Function HRows(xx As String)
BeginRow = 2
EndRow = 10
' HideRows
For RowCnt = BeginRow To EndRow
Cells(RowCnt,ChkCol).EntireRow.Hidden = True
Next RowCnt
End Function


2014-05-23 04:36:23

Ash

Thank you so much Allen, exactly what I needed!

Wanted to hide rows that were made blank by a formula but all other macros I found needed the cells to be actually blank (i.e. it saw the formula itself and considered the cell NOT blank), but this allowed to to use "" as my search item.

Thank you very much, lovely and neat.


2014-05-09 19:34:33

Mark

This works great for hiding empty rows, however is there a macro that will unhide the hidden cells if data is imported into one of the hiddens cells by a sum formula running in one of the hidden cells?


2014-04-16 05:54:02

Vincent

Thanks!
I was looking exactly for this, worked like a charm.
Thanks a lot!!


2013-04-30 06:39:00

Janusz

Hello Allen,
I need to hide any row in range 2-500 if any cells from column "I" show text: "delivered" and if shows anything else the row should be visible. What macro I should use?

Thank you.
Janusz


2013-03-22 04:04:58

Richard Christopher G. Abella

Amazing! Works like a charm. Exactly what I was thinking it would do.

Thank you Mr. Wyatt


2013-02-12 05:41:43

Barry Fitzpatrick

Frank the subroutine you need is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim PassWord As String

PassWord = "Password" 'Change this to be password for the Worksheet

If Not Intersect(Target, Range("A11")) Is Nothing Then
ActiveSheet.Unprotect PassWord
If Left(Range("A11"), 1) = "h" Then
ActiveSheet.Rows(11).Hidden = True
Else
ActiveSheet.Rows(11).Hidden = False 'delete line this if you don't want
'to be able to restore the row
End If
ActiveSheet.Protect PassWord
End If

End Sub

This code should be placed on the code page for the worksheet concerned. It doesn't rely a value in Cell A12 it is triggered by changes in the Cell A11 alone. Deleting the value in Cell A11 will restore the row.

You do need to embed the password for the worksheet into the routine, unless the protection on the worksheet allows the User to "Format Rows" which could result in other formatting issues.
If you embed the password then you should set a password on the VBA code to prevent unauthorised viewing (Note: Excel & VBA passwords are very weak an can easily be broken/bypassed).


2013-02-11 08:24:37

Frank Rowley

Hi there, I have a problem that I am told needs sub routine, no idea how to do this, yet to me there should be a simple code to do what I need.
This is what I need. If cell a11=”hide” or “h” then completely hide row a11. This would be a users input to an un-locked cell in a protected sheet.
Cell a11 is Unlocked for the user to enter info. Cell a12 which is locked monitors the input from the user on cell a11. If the user inputs “H” into cell a11 then the result would be that cell a12 would be true and close that row. This would be a user decision to clean up none active people from the sheet display. Delete row would not work as other spread sheets link to this location...


2012-08-23 18:59:58

Gilberto

Thanks for the insight
've been in other "forums" and things were unnecessarily complicated...
I have adapted your routine to my task and it is simple and works fine


2012-05-17 12:20:51

Bruce

Thanks Allen. This worked perfectly. I setup a lookup based on a data validation that set a column to True/False and ran your code on the column True/False and based on the data value being false it hid the row. Very nice. Thank you.


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share