I need to use a single cell to show/hide other rows. For instance if you put
a "6" in cell B1 it would show the next 12 rows and hide the rest. If you
put in a "10" it would show the next 20 rows and hide the rest, etc.

I'm new to writing macros, sorry!

Not sure exactly what you want, but try this.

Sub HideUnhide()
Rows("2:" & Rows.Count).Hidden = True
If Range("B1") = 6 Then
Rows("2:13").Hidden = False
ElseIf Range("B1") = 10 Then
Rows("2:21").Hidden = False
End If
End Sub


"New2Macros" wrote:

I need to use a single cell to show/hide other rows. For instance if you put
a "6" in cell B1 it would show the next 12 rows and hide the rest. If you
put in a "10" it would show the next 20 rows and hide the rest, etc.

I'm new to writing macros, sorry!

That is close. What I need is that you can choose from 1 to 35 (maybe in a
pull down), and have it show the twice that many rows. So if you pick 2 it
would show rows 3 through 6 and hide all the other pre-populated rows.

Also when I plugged in that macro I'm getting an error '1004'. "Unable to
set the hidden property of the range class."



"New2Macros" wrote:

I need to use a single cell to show/hide other rows. For instance if you put
a "6" in cell B1 it would show the next 12 rows and hide the rest. If you
put in a "10" it would show the next 20 rows and hide the rest, etc.

I'm new to writing macros, sorry!

I don't completely understand your criteria. From your example, if the user
picks 2, how do you know to start showing rows at Row 3? And if you hide
everything except for Rows 3 through 6, then B1 will be hidden and you won't
be able to change the number in it again without manually unhiding it. Can
you give us more details on how you want this functionality to work (keep in
mind that no one here has any idea what you want to do, so you have to tell
us).

Rick


"New2Macros" wrote in message
...
That is close. What I need is that you can choose from 1 to 35 (maybe in
a
pull down), and have it show the twice that many rows. So if you pick 2
it
would show rows 3 through 6 and hide all the other pre-populated rows.

Also when I plugged in that macro I'm getting an error '1004'. "Unable to
set the hidden property of the range class."



"New2Macros" wrote:

I need to use a single cell to show/hide other rows. For instance if you
put
a "6" in cell B1 it would show the next 12 rows and hide the rest. If
you
put in a "10" it would show the next 20 rows and hide the rest, etc.

I'm new to writing macros, sorry!

New2Macros:

Sorry I'm not being clear enough. Say in cell A1 you want to enter the
number of rows being shown in the spreadsheet. A1 should always be shown, of
course, and each number entered in A1 will show double the amount of rows
below. Each row starting with A2 will have information entered previously,
so we are just displaying, or hiding those rows below. For instance: if I
want only 2 rows displaying below, I would enter a "1" in cell A1. If I want
10 rows to show, I would enter "5" in A1, and so on. A1 probably will have a
pull down.



"Rick Rothstein (MVP - VB)" wrote:

I don't completely understand your criteria. From your example, if the user
picks 2, how do you know to start showing rows at Row 3? And if you hide
everything except for Rows 3 through 6, then B1 will be hidden and you won't
be able to change the number in it again without manually unhiding it. Can
you give us more details on how you want this functionality to work (keep in
mind that no one here has any idea what you want to do, so you have to tell
us).

Rick


"New2Macros" wrote in message
...
That is close. What I need is that you can choose from 1 to 35 (maybe in
a
pull down), and have it show the twice that many rows. So if you pick 2
it
would show rows 3 through 6 and hide all the other pre-populated rows.

Also when I plugged in that macro I'm getting an error '1004'. "Unable to
set the hidden property of the range class."



"New2Macros" wrote:

I need to use a single cell to show/hide other rows. For instance if you
put
a "6" in cell B1 it would show the next 12 rows and hide the rest. If
you
put in a "10" it would show the next 20 rows and hide the rest, etc.

I'm new to writing macros, sorry!

Okay, the 'start at Row 3' was a mistype then. By the way, you originally
said B1 would have the number in it, but now are saying A1. No problem, I'll
use A1 in my example. Give this a try and see if it does what you want.
Right-click the tab on the worksheet where you want to have this
functionality and select View Code from the popup menu that appears. This
will take you into the VBA editor and present an already opened code window
to you. Copy/Paste the following into that code window...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Range("2:" & Rows.Count).EntireRow.Hidden = False
Range(CStr(2 + 2 * Range("B1").Value) & ":" & _
Rows.Count).EntireRow.Hidden = True
End If
End Sub

Now, go back to the worksheet and type different numbers into A1 to see if
the code is doing what you want. You can put a drop-down list into A1 if you
want... it will not matter to the code how the number gets into the cell.

Rick


"New2Macros" wrote in message
...
New2Macros:

Sorry I'm not being clear enough. Say in cell A1 you want to enter the
number of rows being shown in the spreadsheet. A1 should always be shown,
of
course, and each number entered in A1 will show double the amount of rows
below. Each row starting with A2 will have information entered
previously,
so we are just displaying, or hiding those rows below. For instance: if
I
want only 2 rows displaying below, I would enter a "1" in cell A1. If I
want
10 rows to show, I would enter "5" in A1, and so on. A1 probably will
have a
pull down.



"Rick Rothstein (MVP - VB)" wrote:

I don't completely understand your criteria. From your example, if the
user
picks 2, how do you know to start showing rows at Row 3? And if you hide
everything except for Rows 3 through 6, then B1 will be hidden and you
won't
be able to change the number in it again without manually unhiding it.
Can
you give us more details on how you want this functionality to work (keep
in
mind that no one here has any idea what you want to do, so you have to
tell
us).

Rick


"New2Macros" wrote in message
...
That is close. What I need is that you can choose from 1 to 35 (maybe
in
a
pull down), and have it show the twice that many rows. So if you pick
2
it
would show rows 3 through 6 and hide all the other pre-populated rows.

Also when I plugged in that macro I'm getting an error '1004'. "Unable
to
set the hidden property of the range class."



"New2Macros" wrote:

I need to use a single cell to show/hide other rows. For instance if
you
put
a "6" in cell B1 it would show the next 12 rows and hide the rest. If
you
put in a "10" it would show the next 20 rows and hide the rest, etc.

I'm new to writing macros, sorry!

This seemed to work right away, but now I'm getting an "argument not
optional" message when I put a number. What could this mean?




"Rick Rothstein (MVP - VB)" wrote:

Okay, the 'start at Row 3' was a mistype then. By the way, you originally
said B1 would have the number in it, but now are saying A1. No problem, I'll
use A1 in my example. Give this a try and see if it does what you want.
Right-click the tab on the worksheet where you want to have this
functionality and select View Code from the popup menu that appears. This
will take you into the VBA editor and present an already opened code window
to you. Copy/Paste the following into that code window...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Range("2:" & Rows.Count).EntireRow.Hidden = False
Range(CStr(2 + 2 * Range("B1").Value) & ":" & _
Rows.Count).EntireRow.Hidden = True
End If
End Sub

Now, go back to the worksheet and type different numbers into A1 to see if
the code is doing what you want. You can put a drop-down list into A1 if you
want... it will not matter to the code how the number gets into the cell.

Rick


"New2Macros" wrote in message
...
New2Macros:

Sorry I'm not being clear enough. Say in cell A1 you want to enter the
number of rows being shown in the spreadsheet. A1 should always be shown,
of
course, and each number entered in A1 will show double the amount of rows
below. Each row starting with A2 will have information entered
previously,
so we are just displaying, or hiding those rows below. For instance: if
I
want only 2 rows displaying below, I would enter a "1" in cell A1. If I
want
10 rows to show, I would enter "5" in A1, and so on. A1 probably will
have a
pull down.



"Rick Rothstein (MVP - VB)" wrote:

I don't completely understand your criteria. From your example, if the
user
picks 2, how do you know to start showing rows at Row 3? And if you hide
everything except for Rows 3 through 6, then B1 will be hidden and you
won't
be able to change the number in it again without manually unhiding it.
Can
you give us more details on how you want this functionality to work (keep
in
mind that no one here has any idea what you want to do, so you have to
tell
us).

Rick


"New2Macros" wrote in message
...
That is close. What I need is that you can choose from 1 to 35 (maybe
in
a
pull down), and have it show the twice that many rows. So if you pick
2
it
would show rows 3 through 6 and hide all the other pre-populated rows.

Also when I plugged in that macro I'm getting an error '1004'. "Unable
to
set the hidden property of the range class."



"New2Macros" wrote:

I need to use a single cell to show/hide other rows. For instance if
you
put
a "6" in cell B1 it would show the next 12 rows and hide the rest. If
you
put in a "10" it would show the next 20 rows and hide the rest, etc.

I'm new to writing macros, sorry!