Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows of active selection
Hello--
I have a huge amount of information that comes in monthly that is always formatted/positioned in the exact same way. Only differences are cell values. I'd like to build a macro that would allow the user to automatically hide rows that contain a 0 for column's N and K. However there are blocks of data for which I would not want to hide, so I was thinking if the user can just select the relevant data he/she wants to run the macro on, that'd be best. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows of active selection
See if this is what you are looking for, if not let me know what needs
to be changed. -Andrew Sub RangeHideRows() Dim Rng As Range On Error Resume Next Application.DisplayAlerts = False 'Have the user select a range to loop through Set Rng = Application.InputBox(Prompt:="Please select a range to run macro on.", _ Title:="SPECIFY RANGE", Type:=8) On Error GoTo 0 Application.DisplayAlerts = True 'If the selection is blank, exit routine If Rng Is Nothing Then Exit Sub Else 'Get the first row Dim intrownum As Integer intrownum = Rng.Row 'loop through each row For Each Row In Rng If Range("K" & intrownum) = 0 And Range("N" & intrownum) = 0 Then Range("a" & intrownum).EntireRow.Hidden = True End If 'counter for next row intrownum = intrownum + 1 Next Row 'reset counter intrownum = 0 End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows of active selection
On Sep 19, 4:07*pm, chrismv48
wrote: Hello-- I have a huge amount of information that comes in monthly that is always formatted/positioned in the exact same way. *Only differences are cell values. I'd like to build a macro that would allow the user to automatically hide rows that contain a 0 for column's N and K. *However there are blocks of data for which I would not want to hide, so I was thinking if the user can just select the relevant data he/she wants to run the macro on, that'd be best.. Any suggestions? Why write a macro? I'd use data/filter/autofilter and then on row 1 in column N I'd click on the drop-down and set a custom filter "not equal" and a value of 0. I'd do the same thing in column K. The "not equal" condition will allow negative amounts to display. Don in Montana |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows of active selection
Because the reports are generated with many rows already hidden for various
reasons. When you enable a filter, it unhides everything. So then I'd have to go thru and apply a bunch of filters to get it back to where I wanted in the first place. Andrew--That code ran from beginning to end, but as far as I can tell, it didn't do anything ??? "HappySenior" wrote: On Sep 19, 4:07 pm, chrismv48 wrote: Hello-- I have a huge amount of information that comes in monthly that is always formatted/positioned in the exact same way. Only differences are cell values. I'd like to build a macro that would allow the user to automatically hide rows that contain a 0 for column's N and K. However there are blocks of data for which I would not want to hide, so I was thinking if the user can just select the relevant data he/she wants to run the macro on, that'd be best.. Any suggestions? Why write a macro? I'd use data/filter/autofilter and then on row 1 in column N I'd click on the drop-down and set a custom filter "not equal" and a value of 0. I'd do the same thing in column K. The "not equal" condition will allow negative amounts to display. Don in Montana |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows of active selection
On Sep 22, 10:25*am, chrismv48
wrote: Because the reports are generated with many rows already hidden for various reasons. *When you enable a filter, it unhides everything. *So then I'd have to go thru and apply a bunch of filters to get it back to where I wanted in the first place. Andrew--That code ran from beginning to end, but as far as I can tell, it didn't do anything ??? "HappySenior" wrote: On Sep 19, 4:07 pm, chrismv48 wrote: Hello-- I have a huge amount of information that comes in monthly that is always formatted/positioned in the exact same way. *Only differences are cell values. I'd like to build a macro that would allow the user to automatically hide rows that contain a 0 for column's N and K. *However there are blocks of data for which I would not want to hide, so I was thinking if the user can just select the relevant data he/she wants to run the macro on, that'd be best.. Any suggestions? Why write a macro? I'd use data/filter/autofilter and then on row 1 in column N I'd click on the drop-down and set a custom filter "not equal" and a value of 0. I'd do the same thing in column K. The "not equal" condition will allow negative amounts to display. Don in Montana- Hide quoted text - - Show quoted text - Thanks for the additional facts. When looking at the filtered data, copy the entire ws to a new sheet. The new sheet will exclude whatever your filter hid. You can now apply a filter to that sheet without having to redo the previously set filter. Don in Montana. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Active range/selection? | Excel Worksheet Functions | |||
Object Type of a selection... counting rows in a selection | Excel Programming | |||
Changing active selection cursor | Excel Discussion (Misc queries) | |||
Hide Rows based on Time Selection in timesheet. | Excel Programming | |||
Looping thru the active selection | Excel Programming |