Hide unused rows based on column info

[Becki]

Help is much appreciated. I need a check box or easily clicked way to
hide/unhide a range of rows based on the data in a column.

Example: Rows 1:130 contain product info. Column D contains a chosen
Quantity (number). I would like to easily show/hide any rows that do not have
a number in Column D.

Thanks for your assistance!

 

[Jacob Skaria]

Try the below macro..If you are new to macros set the Security level to
low/medium in (Tools|Macro|Security). From workbook launch VBE using
short-key Alt+F11. From menu 'Insert' a module and paste the below code.
Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro()>
You can assign a hot key for this macro or place a autoshape (say bevel) from
DrawingToolbar> and right click>Assign macro...to make it work at click.

Sub Mac()
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If IsNumeric(Range("D" & lngRow)) = False Then _
Rows(lngRow).Hidden = True
Next
End Sub

If this post helps click Yes

 

[Becki]

Jacob - thank you. I followed your directions (you are very helpful to the
amateur) but it doesn't seem to do anything at all when I hit run/play. I
haven't yet assigned a button but nothing is hiding with this.

Becki

 

[Justin Case]

Hi Becki,

Here's a possible solution using autofilter. Select any cell in the
list then...

Sub(FilterD)
'assuming the list starts in column A...
Selection.AutoFilter Field:=4, Criteria1:=">0", Operator:=xlAnd
End Sub

Autofilter will remain on after the macro has run.

To turn it off with a macro

Sub FilterOff()
Selection.AutoFilter
End Sub

All of the above can easily be assigned to toolbars, buttons, etc. (or
can be accessed from the menu of course).

Regards,
Justin

 

[Becki]

Thank you, Justin. This is just beyond me. I haven't used macros or anything
else and I can't figure this out. I'll keep reading and see if I can find
something that helps. Thank you again for your kind help.

Becki

 

[Ashish Mathur]

Hi,

Suppose your data is in range A5:A500 (where A5 contins the heading). Copy
A5 and paste it in A2. In cell A3, type =isnumber(A6). Now go to filter >
Advanced filter. In the list range select A5:A500 and in the criteria range
select A2:A3. Now simply click on OK. This will show you the cells with
numbers only. To get cells with non numbers, type =not(isnumber(A6)) in
cell A3 and then run the filter >? Advanced filter.

Please remember that the output of advanced filters is not dynamic I.e. if
the data in A6:A500 changes or if the criteria changes, the output will not
change unless you rerun the advanced filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com