Hide unused rows based on column info
 

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!

Hide unused rows based on column info
 

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 clickAssign 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
---------------
Jacob Skaria


"Becki" wrote:

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!

Hide unused rows based on column info
 

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

"Jacob Skaria" wrote:

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 clickAssign 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
---------------
Jacob Skaria


"Becki" wrote:

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!

Hide unused rows based on column info
 

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

Hide unused rows based on column info
 

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

"Justin Case" wrote:

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

Hide unused rows based on column info
 

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

"Becki" wrote in message
...
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!