help... i might need a macro for this.. (hiding a specific # of ro

[Guest]

here's what i have:
a group of rows i like to call the Agroup, Bgroup,, etc, up the Ogroup.
So there is 15 groups of rows.
Each group has 20 rows in them.
The Agroup starts at row25, and ends at row44, where the next group picks
up, and so on.

Here's what i want:
Since i'm not using the cells before A25,
i want A1 to represent how many rows to unhide in the Agroup
A2 to represent how many rows to unhide in the Bgroup,
and so on, up to A15 to represent how many hows to unhide in the Ogroup.

For example: if i type "3" in A1,
STEP1: right away,this would tell excel to unhide the first 3 rows in the
Agroup (A25, A26, A27) if they aren't already.
STEP2: Hide the rest of the rows in the Agroup. (row28 through row43)
then if i type "4" in A2,
excel repeats the same steps. (unhide the first 4 rows in the Bgroup A45 -
A49)
and HIDE the rest of the Agroup (A50 through A64)

The sheet could start off with all rows hidden, or visible, whichever is
easier.
(Preferably i'd like the code to work under either condition)

ANY kind of help would be GREATLY appreciated
and if you can explain what your formula/script does, so i understand.
thanks in advance

 

[Trevor Shuttleworth]

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim StartRow As Long
Dim EndRow As Long
If Intersect(Target, Range("A1:A15")) Is Nothing Then Exit Sub
StartRow = (Target.Row * 20) + 5
EndRow = StartRow + 19
Range(Cells(StartRow, 1), Cells(EndRow, 1)).EntireRow.Hidden = True
Range(Cells(StartRow, 1), Cells(StartRow + Target.Value - 1,
1)).EntireRow.Hidden = False
End Sub

This needs to go in the worksheet class module.

Regards

Trevor

 

[Guest]

Hey, um... Your script works brilliantly, but..
Sorry, i just wanted to use A1:A15 as an example.
The thing is, my actual target range is not in A1:A15
and so other weird things happen.
How would i edit this script, so that the the row of the target range is
irrelevant?

or if thats not possible,
What would i do if my target range is I6:I20 ?

(Meaning, cell I6's value is the number of rows to unhide for the Agroup,
and hiding the rest, Cell I7's number is how many to unhide in the Bgroup,
and so on)

Maybe it would be alot better if i had a button that would bring up a form
for the user to answer these questions (how many rows to unhide in each group)
but this may be too much to ask.

tia, for what ever you can help with

 

[Trevor Shuttleworth]

i just wanted to use A1:A15 as an example

It was a very good and specific example, so you got code that did what you
asked for. If you move the goal posts then you score fewer goals. And, if
you change the rules, you may not get anything like what you expected.

.... other weird things happen ...

And they would be ?

How would i edit this script ...

Remove: If Intersect(Target, Range("A1:A15")) Is Nothing Then Exit Sub

.... if my target range is I6:I20 ...

Change to: If Intersect(Target, Range("I6:I20")) Is Nothing Then Exit Sub

Regards

Trevor

 

[Guest]

ya sry for changing it around...
and no, just replacing A1:A15 with I6:I20 does not work. i tried it and
weird things happen.
I.E. if i type 3 in I6 ..this 'should' have hidden all but row 25,26, and 27
(in the Agroup)
but instead, it hides rows 128-144.

after re-examining the formula, i noticed that it takes target.value's row
as a factor.
so if the target row changes, so does the math result in the formula.
so.. what to do?
the target value's row should be irrelevant, if its possible to do it that
way somehow

If it helps, Here's one way i thought of doing this:
The target value to hide rows in the A-group, label the cell "group1" , and
for Bgroup "group2"
and then somehow strip all but the number, and use that number in the formula.

i.e. StartRow = (Target.Row * 20) + 5
changed to StartRow= (Target.cellname (strip the word "group") *20) + 5
the problem is, i have no idea how to call a cell's label, let alone strip it.

I donno .. w/e method you come up with would be good i guess

 

[Trevor Shuttleworth]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim StartRow As Long
Dim EndRow As Long
If Intersect(Target, Range("I6:I20")) Is Nothing Then Exit Sub
'If Intersect(Target, Range("A1:A15")) Is Nothing Then Exit Sub
' change to reflect start row being 6 rather than 1 (Target.Row - 5)
StartRow = ((Target.Row - 5) * 20) + 5
EndRow = StartRow + 19
Range(Cells(StartRow, 1), Cells(EndRow, 1)).EntireRow.Hidden = True
Range(Cells(StartRow, 1), Cells(StartRow + Target.Value - 1,
1)).EntireRow.Hidden = False
End Sub

 

[Guest]

wow, that was simple..
targetrow -5..

i prolly would've never thought of that lol.
thank you very much. you've saved me a lot of trouble

 

[Trevor Shuttleworth]

You're welcome. Thanks for the feedback

wow, that was simple..
targetrow -5..

i prolly would've never thought of that lol.
thank you very much. you've saved me a lot of trouble