Macro to Hide Rows that contain specific Values...

[diqbal]

I need to create a macro that looks at every row in a worksheet and checks to see if columns C-H all have a value of "-" and if this is true it hides the row. It then keeps running for 9 more worksheets that are identical to the first one for all intents and purposes.

The logical reason for this is that I am creating a sheet that pulls in values from an external source and I want to hide all the rows that have no values so as to not have a lot of rows with just dashes in them.

Please help as I am an extreme newbie to Excel Macros. Thanks.

 

[Greg Truby]

Nate, thank you for those links. Very informative.

Diqbal,

In my very first post on this thread I suggested using AutoFilter. And now, after seeing Tommy Bak comments on the thread that Shades started regarding Replacement Function Speed, I decided this might warrant further investigation and I can only say WOW Tommy was quite right. AutoFilter is the way to go. I also ran my straight looping code against code using .Find and at least for me, the straight looping code ran faster.

Here's the code using autofilter:

<font face=Courier New>    
<SPAN style="color:#00007F">Sub</SPAN> HideUsingAutoFilter()

<SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">Dim</SPAN> rngBottom  <SPAN style="color:#00007F">As</SPAN> Range, rngBig <SPAN style="color:#00007F">As</SPAN> Range, rngBig2 <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> rngToHide1 <SPAN style="color:#00007F">As</SPAN> Range, rngToHide2 <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> t <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN>, f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>

t = Timer

Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets([transpose(row(8:19))])
    ws.Activate
    ws.[b1].EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rngBottom = Range("H65536").End(xlUp)
    <SPAN style="color:#00007F">Set</SPAN> rngBig = Range("B8:" & rngBottom.Address)
    <SPAN style="color:#00007F">Set</SPAN> rngBig2 = Range("B9:" & rngBottom.Address)
    rngBig.AutoFilter
    <SPAN style="color:#00007F">For</SPAN> f = 1 <SPAN style="color:#00007F">To</SPAN> 7
        rngBig.AutoFilter Field:=f, Criteria1:="=0.00%", Operator:=xlOr, Criteria2:="=0.0%"
    <SPAN style="color:#00007F">Next</SPAN> f
    <SPAN style="color:#00007F">Set</SPAN> rngToHide1 = rngBig2.SpecialCells(xlCellTypeVisible)
    rngBig.AutoFilter
    <SPAN style="color:#00007F">For</SPAN> f = 1 <SPAN style="color:#00007F">To</SPAN> 7
        rngBig.AutoFilter Field:=f, Criteria1:="-"
    <SPAN style="color:#00007F">Next</SPAN> f
    <SPAN style="color:#00007F">Set</SPAN> rngToHide2 = rngBig2.SpecialCells(xlCellTypeVisible)
    rngBig.AutoFilter
    <SPAN style="color:#007F00">'rngToHide1.Interior.ColorIndex = 14</SPAN>
    <SPAN style="color:#007F00">'rngToHide2.Interior.ColorIndex = 24</SPAN>
    rngToHide1.EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN>
    rngToHide2.EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Next</SPAN> ws
    
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
Debug.Print "AutoFilter", Timer - t

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    </FONT>

Here were the timing test results:

Run #	Loop	.Find	AutoFilter
1	 41.688 	 51.375 	 2.172 
2	 41.719 	 51.524 	 2.141 
3	 41.466 	 51.500 	 2.156 
4	 41.781 	 51.388 	 2.172 
5	 41.922 	 51.672 	 2.156 
6	 41.906 	 51.734 	 2.156 
7	 41.938 	 51.734 	 2.156 
8	 41.906 	 51.672 	 2.188 
9	 41.938 	 51.750 	 2.156 
10	 41.969 	 51.781 	 2.156 
11	 41.906 	 51.688 	 2.172 
12	 41.859 	 51.594 	 2.172 
13	 41.859 	 51.641 	 2.156 
14	 41.938 	 51.656 	 2.172 
15	 41.769 	 51.594 	 2.172 
16	 41.781 	 51.500 	 2.141 
17	 41.641 	 51.344 	 2.172

 

[geolefty]

Hopefully I followed everything correctly. This is mostly over my head but I am trying to learn. I am just curious.

Is it possible (and maybe faster) to insert a column which would add all of the values from columns C-H and then filter on the "ZERO" value in that newly added column.

then hide that column also.

 

[Legacy 21301]

Hopefully I followed everything correctly. This is mostly over my head but I am trying to learn. I am just curious.

Is it possible (and maybe faster) to insert a column which would add all of the values from columns C-H and then filter on the "ZERO" value in that newly added column.

then hide that column also.

Or insert a column to hold a COUNTIF formula(or maybe SUM) that returns a text value if the row is to be hidden, then hide the relevant rows via SpecialCells, then delete the inserted column.

 

[Greg Truby]

Lefty,

Of course it's possible! As to whether it's faster - try it and let us know!!! (This ain't gospel you know, and there's no warranty to void.)

On another thread where Erik (I forget his surname, but he's in Brussels (I think)) recently cited a similar idea; inserting a column and then using sort. How that idea would compare speed-wise to the idea you suggested or setting multiple Autofilter criteria like above, I wouldn't know without testing and I haven't the time to set up a testing scenario this week.

And I second one of Ponsy's comments: if the insert sum-column approach does pan out to be quicker, I'd then delete if after I was done. No need to leave it lying about cluttering up the place. {Edit}Well, now that I think about about it - there may be a reason to leave it in; if you thought that you'd want to flip back and forth between hiding these rows and unhiding them it'd make sense to leave it in as a handy way to re-hide them.{EndEdit}

If you decide to try out alternatives, let us know how they stack up.

Regards,

 

[Legacy 21301]

All of these methods have been discussed/tested many times on this board.

I'd be willing to bet that given the requirement to hide the rows, auto-filter is probably slightly faster than using a temporary formula to house a formula.

If the requirement was to delete the rows, I'd bet that using a temporary column to house a formula then sorting by that column to put all the rows to be deleted at the bottom, would be faster than using auto-filter.

Also, worth remembering that SpecialCells fails for ranges consisting of more than 8,192 non-contiguous areas.

 

[Mike Tinnion]

This code works perfectly (with some changes) to hide rows which contain (in my case manufacturer names in cells O to W) but I would like to have a message box open to input different names to hide rows by.

Or hide rows if the contents of the cells in that row matched the contents of a cell which can be altered by a data validation list (which can be on the same sheet). I can do the data validation list, it's the VBA code I need.

It would be good to know how to do both options if possible.

Any help would be appreciated.

Mike

Something like this will do it for 1 sheet. I'll amend it for more than one sheet if you like.

Sub HideColumns()
For n = 1 To 65536 'All Rows
With Worksheets("Sheet1")
For c = 3 To 8 'Columns C to H
If .Cells(n, c) <> "-" Then Exit For
If c = 8 Then .Rows.Hidden = True
Next c
End With
Next n
End Sub

 

[HorizonP]

Is there there way to incorporate two variables for the below code (taken from the above code): I would like to do <> for 0 and for "N/A"

If .Cells(Row, Col) <> "-" Then Exit For
If Col = 8 Then .Rows(Row).Hidden = True

 

[jpgallardo]

Hi guys,

I would like request for a help regarding formulating to hide columns or rows.
Here's what I want to do:
(1) It starts with my format with five layers which corresponds 10 columns every 1 layer.
(2) Then, in cell B3 you will input if how many layers you want to work with, let say 3 layers only
(3) Finally, the 4th and 5th layers are going to hide
How can I do that?
Please help me.

Thank you very much.

Best regards,
JP Gallardo

Try copying this. This is only for 3 sheets, but you can amend it for as many as you want (10 altogether?). Just add more Cases in the Select case, and change the names of the sheet to what to you want. Make sure you also change For S = 1 to 10. It may take a few seconds to run, but it'll be quicker than doing it by hand!

Sub HideRowsOnAllSheets()

Dim Sht As Worksheet
Dim S As Byte, Col As Byte
Dim Row As Long
Dim ShtName As String

Application.ScreenUpdating = False

For S = 1 To 3 '1 to number of sheets, eg 3 in this example
Select Case S
Case 1: ShtName = "Sheet1" 'Type the names of each sheet (in "")
Case 2: ShtName = "Sheet2"
Case 3: ShtName = "Sheet3" 'Add other cases, eg Case 4:
End Select 'Make sure the no of Case's is the same as max S

Set Sht = Worksheets(ShtName)
For Row = 1 To 65536 'All Rows
With Sht
For Col = 3 To 8 'Columns C to H
If .Cells(Row, Col) <> "-" Then Exit For
If Col = 8 Then .Rows(Row).Hidden = True
Next Col
End With
Next Row

Next S
Set Sht = Nothing

Application.ScreenUpdating = True
End Sub