Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-09-2015, 11:20 PM
Deane Deane is offline Hide rows in multiple columns based on zero values Windows 7 64bit Hide rows in multiple columns based on zero values Office 2010 64bit
Novice
Hide rows in multiple columns based on zero values
 
Join Date: Jun 2015
Posts: 8
Deane is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-10-2015, 12:35 PM
charlesdh charlesdh is offline Hide rows in multiple columns based on zero values Windows 7 32bit Hide rows in multiple columns based on zero values Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 06-11-2015, 08:41 AM
Deane Deane is offline Hide rows in multiple columns based on zero values Windows 7 64bit Hide rows in multiple columns based on zero values Office 2010 64bit
Novice
Hide rows in multiple columns based on zero values
 
Join Date: Jun 2015
Posts: 8
Deane is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 06-11-2015, 10:35 AM
charlesdh charlesdh is offline Hide rows in multiple columns based on zero values Windows 7 32bit Hide rows in multiple columns based on zero values Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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?
Reply With Quote
  #5  
Old 06-11-2015, 08:51 PM
excelledsoftware excelledsoftware is offline Hide rows in multiple columns based on zero values Windows 8 Hide rows in multiple columns based on zero values Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

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.
Reply With Quote
  #6  
Old 06-13-2015, 05:26 PM
charlesdh charlesdh is offline Hide rows in multiple columns based on zero values Windows 7 32bit Hide rows in multiple columns based on zero values Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 06-17-2015, 05:36 AM
Deane Deane is offline Hide rows in multiple columns based on zero values Windows 7 64bit Hide rows in multiple columns based on zero values Office 2010 64bit
Novice
Hide rows in multiple columns based on zero values
 
Join Date: Jun 2015
Posts: 8
Deane is on a distinguished road
Default

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?
Reply With Quote
  #8  
Old 06-17-2015, 06:29 AM
NoSparks NoSparks is offline Hide rows in multiple columns based on zero values Windows 7 64bit Hide rows in multiple columns based on zero values Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 821
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

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.
Reply With Quote
  #9  
Old 06-17-2015, 07:23 AM
Deane Deane is offline Hide rows in multiple columns based on zero values Windows 7 64bit Hide rows in multiple columns based on zero values Office 2010 64bit
Novice
Hide rows in multiple columns based on zero values
 
Join Date: Jun 2015
Posts: 8
Deane is on a distinguished road
Default

Hi NoSparks,

It selects the first cell with a zero value?
Reply With Quote
  #10  
Old 06-17-2015, 07:43 AM
NoSparks NoSparks is offline Hide rows in multiple columns based on zero values Windows 7 64bit Hide rows in multiple columns based on zero values Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 821
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

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.
Reply With Quote
  #11  
Old 06-17-2015, 11:04 AM
charlesdh charlesdh is offline Hide rows in multiple columns based on zero values Windows 7 32bit Hide rows in multiple columns based on zero values Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 06-17-2015, 11:28 PM
Deane Deane is offline Hide rows in multiple columns based on zero values Windows 7 64bit Hide rows in multiple columns based on zero values Office 2010 64bit
Novice
Hide rows in multiple columns based on zero values
 
Join Date: Jun 2015
Posts: 8
Deane is on a distinguished road
Default

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.
Attached Files
File Type: docx Hide zeros.docx (44.4 KB, 3 views)
Reply With Quote
  #13  
Old 06-18-2015, 10:10 AM
NoSparks NoSparks is offline Hide rows in multiple columns based on zero values Windows 7 64bit Hide rows in multiple columns based on zero values Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 821
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

That's a word document, doesn't let us see what's really in the cells.
Reply With Quote
  #14  
Old 06-18-2015, 10:37 AM
charlesdh charlesdh is offline Hide rows in multiple columns based on zero values Windows 7 32bit Hide rows in multiple columns based on zero values Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #15  
Old 06-19-2015, 01:28 AM
Deane Deane is offline Hide rows in multiple columns based on zero values Windows 7 64bit Hide rows in multiple columns based on zero values Office 2010 64bit
Novice
Hide rows in multiple columns based on zero values
 
Join Date: Jun 2015
Posts: 8
Deane is on a distinguished road
Default

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
Reply With Quote
Reply

Tags
hide, rows and columns, zero values

Thread Tools
Display Modes


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
Hide rows in multiple columns based on zero values 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
Hide rows in multiple columns based on zero values Insert values from multiple rows based on value in one column pachmarhi Excel 3 07-18-2014 09:57 PM
Hide rows in multiple columns based on zero values 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft