using a value in a cell to hide rows

T

timmtamm

I want to make a macro that will recognize a value in a cell and then hide
rows that I don't need.

for example: cell is J20

If I put into the cell the value of "A1" then hide rows 11:20
"A2" hides rows 21:30
"B1" hides rows 11:30
and of course nothing in the cell does not hide anything.

I found a little format in the FAQ's section, but it was not very helpful. I
adusted it to try to see if it would work for me, but upon trying it, nothing
happened. Either it doesn't automatically tie itself to the cell and I have
to activate the thing or I'm not doing it right. Most likely the latter.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("J20")) Is Nothing Then
Exit Sub
Else
Rows("11:20").Select
Selection.EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$10"

End Sub
 
R

RadarEye

Hi TimmTamm,

You have to keep in mind that the Worksheet_Change macro only reacts
on cells changed by a user.
And not on cells changed by a function in the cell.

In Excel2003 I have created this:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("J20")) Is Nothing Then
Exit Sub
Else
Select Case Target.Value
Case "A2"
Rows("21:30").Select
Selection.EntireRow.Hidden = True
Range("A10").Select
Case "B1"
Rows("11:30").Select
Selection.EntireRow.Hidden = True
Range("A10").Select
End Select
End If
End Sub


This works fine for me.

HTH,

Wouter
 
G

Gary''s Student

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("J20")
If Intersect(t, r) Is Nothing Then Exit Sub
Rows("1:" & Rows.Count).EntireRow.Hidden = False
v = r.Value
If v = "A1" Then
Rows("11:20").EntireRow.Hidden = True
End If
If v = "A2" Then
Rows("21:30").EntireRow.Hidden = True
End If
If v = "B2" Then
Rows("11:30").EntireRow.Hidden = True
End If
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
D

Don Guillett

Right click sheet tab>view code>insert this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$J$2" Then Exit Sub
Rows.Hidden = False
Select Case Target
Case "A1": x = "11:20"
Case "A2": x = "21:30"
Case "B1": x = "11:30"
Case Else
Exit Sub
End Select
Rows(x).Hidden = True
End Sub

'If I put into the cell the value of "A1" then hide rows 11:20
'"A2" hides rows 21:30
'"B1" hides rows 11:30
'and of course nothing in the cell does not hide anything.
 
T

timmtamm

One key element I didn't mention.

If I return the value to nothing or if I put in some other value that I
don't want things hidden on, I wan't the spreadsheet to revert back to normal
(not hiding anything). I've been playing with the code and I haven't figured
out that last part.
 
T

timmtamm

using this program, how then do I print without printing a page for the
hidden area. (This was kinda a part of the original post
[ActiveSheet.PageSetup.PrintArea = "$A$1:$I$10"] I just didn't write down
that it was part of the requirement.)
Page breaks are set up at rows 10, 20, 30.
 
D

Don Guillett

If you are printing a1:i10 why bother to hide rows 11>>>
Perhaps a fuller explanation.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
timmtamm said:
using this program, how then do I print without printing a page for the
hidden area. (This was kinda a part of the original post
[ActiveSheet.PageSetup.PrintArea = "$A$1:$I$10"] I just didn't write down
that it was part of the requirement.)
Page breaks are set up at rows 10, 20, 30.

Don Guillett said:
Right click sheet tab>view code>insert this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$J$2" Then Exit Sub
Rows.Hidden = False
Select Case Target
Case "A1": x = "11:20"
Case "A2": x = "21:30"
Case "B1": x = "11:30"
Case Else
Exit Sub
End Select
Rows(x).Hidden = True
End Sub

'If I put into the cell the value of "A1" then hide rows 11:20
'"A2" hides rows 21:30
'"B1" hides rows 11:30
'and of course nothing in the cell does not hide anything.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
Click to expand...
 
T

timmtamm

I will always print A1:I10 in all the spreadsheets, but sometimes I will also
want down to I20, (thus the area's I will be hiding are rows 21-30). Then,
sometimes I will want to print down to I30, but not rows 11-20 (11-20 will be
hidden). Excel will however print a blank page in the place of the hidden
11-20, and 21-30 will be page 3. Sometimes I will want to print the whole
thing. It will depend upon what parts of the spreadsheet I need to print. I
was planning on adding more lines (conditional to each value) to adjust the
print layout accordingly.

Don Guillett said:
If you are printing a1:i10 why bother to hide rows 11>>>
Perhaps a fuller explanation.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
timmtamm said:
using this program, how then do I print without printing a page for the
hidden area. (This was kinda a part of the original post
[ActiveSheet.PageSetup.PrintArea = "$A$1:$I$10"] I just didn't write down
that it was part of the requirement.)
Page breaks are set up at rows 10, 20, 30.

Don Guillett said:
Right click sheet tab>view code>insert this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$J$2" Then Exit Sub
Rows.Hidden = False
Select Case Target
Case "A1": x = "11:20"
Case "A2": x = "21:30"
Case "B1": x = "11:30"
Case Else
Exit Sub
End Select
Rows(x).Hidden = True
End Sub

'If I put into the cell the value of "A1" then hide rows 11:20
'"A2" hides rows 21:30
'"B1" hides rows 11:30
'and of course nothing in the cell does not hide anything.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I want to make a macro that will recognize a value in a cell and then
hide
rows that I don't need.

for example: cell is J20

If I put into the cell the value of "A1" then hide rows 11:20
"A2" hides rows 21:30
"B1" hides rows 11:30
and of course nothing in the cell does not hide anything.

I found a little format in the FAQ's section, but it was not very
helpful.
I
adusted it to try to see if it would work for me, but upon trying it,
nothing
happened. Either it doesn't automatically tie itself to the cell and I
have
to activate the thing or I'm not doing it right. Most likely the
latter.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("J20")) Is Nothing Then
Exit Sub
Else
Rows("11:20").Select
Selection.EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$10"

End Sub
Click to expand...
Click to expand...
Click to expand...
 
T

timmtamm

I decided to take a slightly different approach and have altered the way I
was attempting to accomplish this. I decided to use a different cell, one
that frequently uses values.

The original program I explained, cell ranges to hide and all, was a
simplified version to make things easier.

Using your basic plan I have set up a working version, but still need to
make it so that if the value does not equal .76, .98, or 1.4, it will return
the spreadsheet to the final result of hiding rows 156:217. Further, if there
is an easy way to combine .98 and 1.4 (as the end result is the same), how
can that be done.

Also, with this code, if the value of the cell is .76 and I then print,
excel will also print a blank sheet in place of the rows that are hidden.

Here is the full code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("B20")
If Intersect(t, r) Is Nothing Then
Exit Sub
Else: v = r.Value
If v = 0.98 Then
Rows("156:186").EntireRow.Hidden = False
Rows("187:217").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$186"
End If
If v = 1.4 Then
Rows("156:186").EntireRow.Hidden = False
Rows("187:217").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$186"
End If
If v = 0.76 Then
Rows("187:217").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$217"
Rows("156:186").EntireRow.Hidden = True
End If
If v = "" Then
Rows("156:217").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$155"
End If
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
hide rows in workbook 1
Code to hide rows if cell in another WS is ="No" 11
Hide Co9lumns based on cell value 6
Multiple cells hiding rows, changing print area 0
Multiple cells for changing my spreadsheet? 0
Word macro that can call cells out of Excel for a find and replace 0
Hide rows issue 3

Top