Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update column info based on column info from another worksh | Excel Discussion (Misc queries) | |||
How do you hide unused cells in a spreadsheet? | Excel Discussion (Misc queries) | |||
Macro to Hide rows based on value of column F | Excel Discussion (Misc queries) | |||
Hide Rows based on value | Excel Discussion (Misc queries) | |||
How to hide unused cells when running accumulated totals | Excel Worksheet Functions |