#1
|
|||
|
|||
Hide rows in multiple columns based on zero values
Hi there I have used the code belowto 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 and only hide the row if there are zeros values in the entire range not just column 3?
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 |
#2
|
|||
|
|||
HI,
Welcome to the forum. I did some research and found these codes that may help you. Code:
Sub Hide0() Dim r As Range, x, c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set r = Range("A1:AZ9000")'' this set the range from column A to AZ' '' it should look at each row in the column and hide the "0"''''' Application.ScreenUpdating = False ActiveSheet.Rows.Hidden = False r.EntireRow.Hidden = True For Each c In r If c <> 0 Then c.EntireRow.Hidden = False End If Next c Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Code:
Sub Unhide() '''' this should unhide the rows. Application.ScreenUpdating = False Cells.EntireRow.Hidden = False Application.ScreenUpdating = True End Sub |
#3
|
|||
|
|||
Thanks for this!
I am however getting a type mismatch error when running the macro? It then hides all rows. Do you know why this could be? |
#4
|
|||
|
|||
HI,
The code that I posted worked on my PC. Can you post a copy of your file? Also which line of the code did it Highlight? |
#5
|
|||
|
|||
Mismatch errors will always happen if there are any cells being checked with the value #N/A I had to learn this the hard way. If you have any cells with that value the code will break each time unless an on error resume next is put in which I rarely encourage.
|
#6
|
|||
|
|||
Hhi,
With this code you should not get a "Mismatch". You may need to change the columns that you are looking at. Code:
Sub Hide_rows() Dim LastRow As Long Dim Rng As Range LastRow = Range("A65536").End(xlUp).Row ' Set Rng = Range("A2:P" & LastRow) 'change "P" to your last coulum Application.ScreenUpdating = False For Each cell In Rng If cell.Value = "0" Then cell.EntireRow.Hidden = True End If Next cell Application.ScreenUpdating = True End Sub |
#7
|
|||
|
|||
Hi there thanks for the replies.
I am still getting a type mismatch with the updated code above. After the type mismatch error it only hides the first row. Is this not an excel plug in issue? |
#8
|
|||
|
|||
The macro Charles provided runs fine for me.
The macro will be hiding the row prior to the mismatch. Try changing the code line of cell.EntireRow.Hidden = True to cell.Select and run the macro. This time when you get the mismatch error I'd suspect whatever is in the next cell to be selected to be causing the problem. Have a look and see what's there. |
#9
|
|||
|
|||
Hi NoSparks,
It selects the first cell with a zero value? |
#10
|
|||
|
|||
Can you post an example file that has this problem ?
Would be much easier seeing the actual problem than to keep trying to guess what the problem is. |
#11
|
|||
|
|||
Hi,
As Nosparks suggested can you provide a example. I use a "Mac" and do not get the "Mismatch". Also, make sure you do not have "Merged" cells. |
#12
|
|||
|
|||
I found a hidden column that had a ref in it So now the code runs without a mismatch but it is still hiding rows that have values in them. It should only hide the row if all cells in that row are zero.. ie. no values in that row.
See attachment for example of layout. |
#13
|
|||
|
|||
That's a word document, doesn't let us see what's really in the cells.
|
#14
|
|||
|
|||
Hi,
Which code are you using? I believe the second code I posted will hide any row that has a "0" value. The first code I believe should only hide the row if all of the row has a value of "0". Try the first code. If it does not work please post a copy of your file so we can see how it's formatted. You can redact vital information. |
#15
|
|||
|
|||
Ok I have reverted to the first code.
Because of the words in the middle column the code is not working (see word document to see the words I speak of). I think the code sees the words as non zeros so wont hide those. Can the code be told to ignore the words? Code currently using: Sub Hide0() Dim r As Range, x, c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set r = Range("A1:I250") Application.ScreenUpdating = False ActiveSheet.Rows.Hidden = False r.EntireRow.Hidden = True For Each c In r If c <> 0 Then c.EntireRow.Hidden = False End If Next c Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub |
Tags |
hide, rows and columns, zero values |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA Code to Hide columns based on selection on prior sheet | Silver1379 | Excel Programming | 0 | 04-15-2015 08:40 AM |
I need to add multiple values based on multiple criteria in a cell not sure what to do | AUHAMM | Excel | 3 | 10-27-2014 09:11 PM |
Insert values from multiple rows based on value in one column | pachmarhi | Excel | 3 | 07-18-2014 09:57 PM |
Hide Rows and Update Chart based on cell value | ubns | Excel Programming | 5 | 05-07-2012 05:44 AM |
Conditional Formatting to Hide Rows or Columns? | sczegus | Excel | 0 | 09-26-2006 04:17 PM |