PDA

View Full Version : [SOLVED] Conditional hiding of rows Part II



K. Georgiadis
04-07-2005, 11:45 AM
Previously I got the following code from DRJ, to hide rows in two worksheets depending on the value in Cell D1 in another worksheet:


Option Explicit

Private Sub Worksheet_Calculate()
Dim i As Long
i = Range("D1").Value
Sheets("Sales").Cells.EntireRow.Hidden = False
Sheets("Margin").Cells.EntireRow.Hidden = False
Sheets("Sales").Range("A" & 3 + i & ":A17").EntireRow.Hidden = True
Sheets("Sales").Range("A" & 21 + i & ":A35").EntireRow.Hidden = True
Sheets("Margin").Range("A" & 3 + i & ":A17").EntireRow.Hidden = True
Sheets("Margin").Range("A" & 21 + i & ":A35").EntireRow.Hidden = True
End Sub

Got a little more complicated: The sheet ("Control Panel") containing this code will now have a second set of rows, starting with B25, with Consumer market segment names. Cell D25 uses COUNTA to determine how many rows in this new data set are populated.

So, now I also need to hide the green-shaded rows in "Sales" and "Margin" that are populated with zeros. Can I add a separate Sub, analogous to the above, to hide the additional cells or do I need a consolidated procedure for the two data sets?

I have attached a small zip file to illustrate the modified design.

dcardno
04-07-2005, 12:59 PM
I generalized the routines a bit, as follows:


Sub HideZeroRows()
Dim rngCll As Range
Dim rngTestRange As Range
Set rngTestRange = Intersect(ActiveSheet.UsedRange, Range("B1").EntireColumn)
Application.ScreenUpdating = False
rngTestRange.EntireRow.Hidden = False
For Each rngCll In rngTestRange
If rngCll.HasFormula And IsNumeric(rngCll) Then
rngCll.EntireRow.Hidden = True
End If
Next rngCll
set rngTestRange = Nothing
Application.ScreenUpdating = True
End Sub

Sub ShowAllRows()
ActiveSheet.UsedRange.EntireRow.Hidden = False
End Sub


and attached them to two buttons in the "Margin" sheet.

The routine will hide any rows in column B that contain a formula if that formula evaluates to a number (the formulas that refer to text values will fail the "IsNumeric" test). The obvious alternative is to test for "zero values" - but the blank spaces evaluate to zero - this way you will maintain the vertical spacing between sections. This will not work if you intend to insert other formulas in column B. I set it up to apply to all rows in Column B (within the used range of the worksheet) so that it can accomodate the next addition of product categories.

As set up, this must be run manually (and the routine is in a standard module). If you wanted to have it run automatically, and apply to both the "Sales" and "Margin" sheets, you would have to place it in the workbook module as an "On_Calculate" routine (that's the way Jake set it up). As well, you would want it to run against each sheet, so it would look something like this (warning, this is 'on the fly' code!):



Option Explicit
Private Sub Worksheet_Calculate()
Dim rngCll As Range
Dim rngTestRange As Range
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In Worksheets
If sht.Name <> "Control Panel" Then
Set rngTestRange = Intersect(sht.UsedRange, sht.Range("B1").EntireColumn)
rngTestRange.EntireRow.Hidden = False
For Each rngCll In rngTestRange
If rngCll.HasFormula And IsNumeric(rngCll) Then
rngCll.EntireRow.Hidden = True
End If
Next rngCll
End If
Next sht
Application.ScreenUpdating = True
End Sub

This will have to be placed in the "workbook" code. I have attached the sheet with the first version of the code (and Jake's work deleted:( )

Cheers,

Dean

K. Georgiadis
04-07-2005, 06:18 PM
That's a viable approach but, knowing the users, I am leaning toward a solution that does not leave any decisions to them. If I could find a way to adapt Jake's code to handle two data sets in the control panel, hiding the appropriate rows in "Sales" and "Margin" automatically I would be home free

dcardno
04-07-2005, 08:56 PM
That's a viable approach but, knowing the users

Those darned users!

I have attached a version that implements my second suggestion, so that the hiding (and un-hiding) of rows is automatic whenever you make an entry in the "Control Panel" sheet - I think that's a better way to go, and I don't see what even a determined (ab)user could do to it.

If you have other reasons for code more along the lines of what you are using now, I would make a couple of suggestions. As that code stands, it reads the value in D1 - the formula to return the number of non-blank entries in the first block of cells. You will need it to read the coresponding formula in the second block (currently in cell D25), and then execute similar operations. It is in the nature of spreadsheets that rows will be inserted or deleted, and a hard-coded reference to cell D25 would eventually break. Instead, I would give a name to each of these cells (ie, two named ranges, each of one cell) so that you could read their values in VBA. The second thing I would do is to generalize the code that reads:


Sheets("Sales").Range("A" & 3 + i & ":A17").EntireRow.Hidden = True
Sheets("Sales").Range("A" & 21 + i & ":A35").EntireRow.Hidden = True

(and the equivalent rows applied to the "Margins" sheet)

by picking up the appropriate rows from the "row" property of the named ranges. I can put something together if you would like, although you will understand it better if you have a whack at it yourself, first.

The code would look something like this:


dim iIndCount as integer
dim iConsCount as integer
dim iIndRow as Integer
dim iConsRow as Integer
iIndCount = [IndustrialList] 'this is the named range in D1
iIndRow = [industrialList].row
iConsCount = [ConsumerList] 'this is the named range in D25
iConsRow = [ConsumerList].row
Sheets("Sales").Range("A" & iIndRow + iIndCount &_
":A" & iConsRow - 3).EntireRow.Hidden = True
'note that this only deals with the first block of
'cells on the "Sales" (and "Margin") tabs
Sheets("Sales").Range("A" & iConsRow + iConsCount &_
":A" & iConsRow + iConsCount + 10).EntireRow.Hidden = True
'this would handle the Consumer block on those
'sheets, if you didn't have a second listing of industrial categories.


Note that as it is, the count of industrial categories is offset by one row from the list of industrial categories, while the count of consumer categories is on the same row as the first entry in the list - you should probably change that if you are going to follow this approach. The last "- 3" and "+ 10" in the rows to be hidden is to allow for the blank spaces between the last industrial category listing and the first consumer product and then to make sure that you hide all the unused rows after the consumer categories listing.

To handle the multiple industrial listings you will have to set up another value (either as a constant or read off the sheets somewhere) to increment the rows that you are going to refer to when you are hiding them - that would lead to the second and third lines looking like:



Sheets("Sales").Range("A" & iIndRow + iIndCount + Incr &_
":A" & iConsRow + Incr - 3).EntireRow.Hidden = True
Sheets("Sales").Range("A" & iConsRow + iConsCount + Incr & _
":A" & iConsRow + iConsCount + Incr + 10).EntireRow.Hidden = True


To top it off, you should probably replace the "+10" entry with some value (again, I would prefer reading it from the sheet somewhere) to indicate how many rows are potentially used by the Consumer product categories.

I still think the attached s/sheet uses an easier approach!

Cheers,

Dean

K. Georgiadis
04-08-2005, 06:02 AM
Wow, Dean, you did a lot of work and I really appreciate it!!! :bow:

The idea of named ranges for cells D1 and D25 is excellent especially since the users in question do have the tendency of messing around with rows!

I will try your other suggestions since I am trying to learn as I do. If I get stuck, I'll give a holler.

Thanks!!

K. Georgiadis
04-08-2005, 08:24 AM
Hello, me again, Dean! I checked the attached workbook and determined that it works fine. I am not knowledgeable enough to understand the improvements inherent in the alternative code listed above. I do however appreciate that naming cells D1 and D25 would be a clear improvement. Can I simply insert that one change in the code currently in use or do you think that the workbook would be more robust if I used the alternative approach suggested above?

Thanks for your help.

dcardno
04-08-2005, 10:10 AM
I guess I should clarify...

The code in the workbook I attached last night is (in my view) the better alternative, so long as you don't put any formulas in column B of the "Sales" or "Margin" sheets other than the ones that refer to the names you want to display. Any formulas in these columns that return a number will result in their row being hidden.

I understood you to say that you wanted to retain the coding approach that Jake had previously provided, and I was trying to illustrate the difficulty of maintaining that code if rows are inserted (or deleted) and when you need to track new categories of product. If I was mistaken, and what you wanted to retain was the "automatic" functioning, then the worksheet attached last night (the last one I sent) still does that. The prior worksheet was intended as more of an illustration of the approach.

Finally, the new worksheet does not rely on the formulas in column D on the "Control Panel" sheet. Although it doesn't hurt to have them there (there is a very negligible performance hit - I promise you won't even see it), they are no longer required. The point of naming the cells was to allow the code to reference them without specifying their location, but since the existing workbook doesn't need them at all, it follows that there is no requirement to name them.

You can either use the file as it sits, cutting and pasting the data from your production file into it, or move the new code into your production file. I will leave the data pasting to you, but if you have lots of data, it will probably be easier to go the other way - moving code rather than data. If you need to move the code from the last one sent to your actual production environment, then you should (WARNING - do this on a copy of your production data file):

Open the file I attached last night and your production file
Open the VBE (Visual Basic Editor) by typing Alt-F11 while the file I provided ("Hide Rows.xls") is active
In the VBE you should see a "Project Explorer" window - it will look like a small windows explorer window, listing all the currently active Excel files.
If you don't see the Project Explorer window, either click on View | Project Explorer or type Ctrl-R
One of the listed files will be "VBA Project (Hide Rows.xls)" - click on any plus signs next to it - that will expand it to show Excel objects in a "tree directory" structure
Left-click on "Sheet1 (Control Panel)" - it will be the first object listed.
This will bring up a dialog box with choices about what you want to do - click on "View Code" (the first option). I have attached a picture of how things should look at this point, just before you click on View Code
Do the same thing to your production file - expand the view to show the Excel objects, and then right click on "Sheet1 (Control Panel)" to show its code.
The code in the "Hide Rows" file will be the one I have been babbling on about, while the code in your production file will be the code that Jake supplied to you.
It will probably be easiest if you maximise the VBE (if you haven't already. It is a matter of personal preference whether you arrange to have both modules visible side-by-side, or are comfortable only seeing one at a time
In the production file (with Jake's code) highlight everything from "Private Sub Worksheet Calculate" to "End Sub." Delete it (yikes - remember, this is just a copy of your file)
Switch to the "Hide Rows" file, and highlight the same range in the "Sheet1 (Control Panel)" module. Copy it - either Crtl-Insert, or Edit | Copy, whatever you are usually comfortable with
Back in the production file, paste the "Hide Rows" code in where Jake's code was just cut.
Close the "Hide Rows" file, without saving any changes
Save the changes to the production file, and close the VBE
Test that the production file works the way you expect
Good luck,

Dean

K. Georgiadis
04-10-2005, 06:12 AM
Thanks Dean. It is perfectly clear now. As far as copying the code is concerned, I can assure you that I have become an expert! I have received so much help from the good folks on this site and I have routinely been using it where applicable. Thanks again for the detailed explanation. VBAX is the best!

dcardno
04-10-2005, 11:52 AM
Hey - I'm glad it worked out. It was an interesting file!

Dean