I didn't know you could program in Excel until I found
this today. I have a problem with a spreadsheet i'm trying
to make. Here's my problem.

Basically I have one spreadsheet which displays all my
information, and another spreadsheet(different tab) which
you input all your information. When you input information
into the 2nd spreadsheet, it transfers it into specific
fields onto the first one. My problem comes in on the 1st
spreadsheet. The 1st spreadsheet will always remain the
same. But the amount of information inputted into the 1st
spreadsheet is different everytime, and ranges from 5 - 20
things inputted. Right now I have set aside about 25
spaces on the 1st sheet, when the information is entered
into the 2nd sheet, it is then displayed on the 1st sheet,
but sometimes I have extra space on the first sheet cause
the information inputted is less than the amount of space
i've allocated for it. My question is this: is there any
way that I can have a program in excel that would hide a
row, if the value in a cell in that row equaled 0. If
anyone can help me with this it would be greatly
appreciated. Thanks

Nate,

Select a cell in the column of interest, then run the macro below.

HTH,
Bernie
MS Excel MVP

Sub HideZeroValueRows()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFind As Double
Dim firstAddress As String


myFind = 0
With ActiveCell.EntireColumn

Set c = .Find(myFind, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
d.Select
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
'Hide the rows of all the found cells
d.EntireRow.Hidden = True
ActiveCell.Select

End Sub



"Nate" wrote in message
...
I didn't know you could program in Excel until I found
this today. I have a problem with a spreadsheet i'm trying
to make. Here's my problem.

Basically I have one spreadsheet which displays all my
information, and another spreadsheet(different tab) which
you input all your information. When you input information
into the 2nd spreadsheet, it transfers it into specific
fields onto the first one. My problem comes in on the 1st
spreadsheet. The 1st spreadsheet will always remain the
same. But the amount of information inputted into the 1st
spreadsheet is different everytime, and ranges from 5 - 20
things inputted. Right now I have set aside about 25
spaces on the 1st sheet, when the information is entered
into the 2nd sheet, it is then displayed on the 1st sheet,
but sometimes I have extra space on the first sheet cause
the information inputted is less than the amount of space
i've allocated for it. My question is this: is there any
way that I can have a program in excel that would hide a
row, if the value in a cell in that row equaled 0. If
anyone can help me with this it would be greatly
appreciated. Thanks

Bernie,

This does work very well in hiding the rows with 0's. My
only problem is this. I'm run the macro which then hides
the rows. The problem is when I put information into the
2nd sheet, the values of the cells change from 0 to
whatever I input in, but the cells still stay hidden, is
there some way which I can run the macro, which will hide
the cells(cause their value equals 0) and then when I
input information(changing their value from 0 to something
else) and the cells would appear again, when it had
information in it? Is this possible? Thanks.

Nate

-----Original Message-----
Nate,

Select a cell in the column of interest, then run the
macro below.

HTH,
Bernie
MS Excel MVP

Sub HideZeroValueRows()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFind As Double
Dim firstAddress As String


myFind = 0
With ActiveCell.EntireColumn

Set c = .Find(myFind, LookIn:=xlValues,
lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
d.Select
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
'Hide the rows of all the found cells
d.EntireRow.Hidden = True
ActiveCell.Select

End Sub



"Nate" wrote in message
...
I didn't know you could program in Excel until I found
this today. I have a problem with a spreadsheet i'm
trying
to make. Here's my problem.

Basically I have one spreadsheet which displays all my
information, and another spreadsheet(different tab)
which
you input all your information. When you input
information
into the 2nd spreadsheet, it transfers it into specific
fields onto the first one. My problem comes in on the
1st
spreadsheet. The 1st spreadsheet will always remain the
same. But the amount of information inputted into the
1st
spreadsheet is different everytime, and ranges from 5 -
20
things inputted. Right now I have set aside about 25
spaces on the 1st sheet, when the information is entered
into the 2nd sheet, it is then displayed on the 1st
sheet,
but sometimes I have extra space on the first sheet
cause
the information inputted is less than the amount of
space
i've allocated for it. My question is this: is there any
way that I can have a program in excel that would hide a
row, if the value in a cell in that row equaled 0. If
anyone can help me with this it would be greatly
appreciated. Thanks


.

Nate,

I think I understand your requirements....

Add this as the first line of the macro (below the dim statements)

Cells.EntireRow.Hidden = False

then copy this event code, right-click on the sheet tab of the sheet where
the rows need to be hidden, select "View Code", and paste into the window
that appears. Then when you select that sheet, the macro to hide the rows
will be run, and the hiding will be based on the updated values.

Private Sub Worksheet_Activate()
HideZeroValueRows
End Sub

HTH,
Bernie
MS Excel MVP


wrote in message
...
Bernie,

This does work very well in hiding the rows with 0's. My
only problem is this. I'm run the macro which then hides
the rows. The problem is when I put information into the
2nd sheet, the values of the cells change from 0 to
whatever I input in, but the cells still stay hidden, is
there some way which I can run the macro, which will hide
the cells(cause their value equals 0) and then when I
input information(changing their value from 0 to something
else) and the cells would appear again, when it had
information in it? Is this possible? Thanks.

Nate

-----Original Message-----
Nate,

Select a cell in the column of interest, then run the
macro below.

HTH,
Bernie
MS Excel MVP

Sub HideZeroValueRows()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFind As Double
Dim firstAddress As String


myFind = 0
With ActiveCell.EntireColumn

Set c = .Find(myFind, LookIn:=xlValues,
lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
d.Select
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
'Hide the rows of all the found cells
d.EntireRow.Hidden = True
ActiveCell.Select

End Sub



"Nate" wrote in message
...
I didn't know you could program in Excel until I found
this today. I have a problem with a spreadsheet i'm
trying
to make. Here's my problem.

Basically I have one spreadsheet which displays all my
information, and another spreadsheet(different tab)
which
you input all your information. When you input
information
into the 2nd spreadsheet, it transfers it into specific
fields onto the first one. My problem comes in on the
1st
spreadsheet. The 1st spreadsheet will always remain the
same. But the amount of information inputted into the
1st
spreadsheet is different everytime, and ranges from 5 -
20
things inputted. Right now I have set aside about 25
spaces on the 1st sheet, when the information is entered
into the 2nd sheet, it is then displayed on the 1st
sheet,
but sometimes I have extra space on the first sheet
cause
the information inputted is less than the amount of
space
i've allocated for it. My question is this: is there any
way that I can have a program in excel that would hide a
row, if the value in a cell in that row equaled 0. If
anyone can help me with this it would be greatly
appreciated. Thanks


.

Bernie,

It's almost perfect and I thank you very much for this.
The Macro will run everytime the sheet is opened, and the
data now appears when I input it. The last thing is this:
The Macro is going to be run on the same number of cells
every time. Where, in the code, can I enter the cells that
I want the macro to always be run on. The code will always
be run on B30 - B59 if that helps. Thanks a lot.

Nate

-----Original Message-----
Nate,

I think I understand your requirements....

Add this as the first line of the macro (below the dim
statements)

Cells.EntireRow.Hidden = False

then copy this event code, right-click on the sheet tab
of the sheet where
the rows need to be hidden, select "View Code", and paste
into the window
that appears. Then when you select that sheet, the macro
to hide the rows
will be run, and the hiding will be based on the updated
values.

Private Sub Worksheet_Activate()
HideZeroValueRows
End Sub

HTH,
Bernie
MS Excel MVP


wrote in message
...
Bernie,

This does work very well in hiding the rows with 0's. My
only problem is this. I'm run the macro which then hides
the rows. The problem is when I put information into the
2nd sheet, the values of the cells change from 0 to
whatever I input in, but the cells still stay hidden, is
there some way which I can run the macro, which will
hide
the cells(cause their value equals 0) and then when I
input information(changing their value from 0 to
something
else) and the cells would appear again, when it had
information in it? Is this possible? Thanks.

Nate

-----Original Message-----
Nate,

Select a cell in the column of interest, then run the
macro below.

HTH,
Bernie
MS Excel MVP

Sub HideZeroValueRows()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFind As Double
Dim firstAddress As String


myFind = 0
With ActiveCell.EntireColumn

Set c = .Find(myFind, LookIn:=xlValues,
lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress
Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
d.Select
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
'Hide the rows of all the found cells
d.EntireRow.Hidden = True
ActiveCell.Select

End Sub



"Nate" wrote in message
...
I didn't know you could program in Excel until I
found
this today. I have a problem with a spreadsheet i'm
trying
to make. Here's my problem.

Basically I have one spreadsheet which displays all
my
information, and another spreadsheet(different tab)
which
you input all your information. When you input
information
into the 2nd spreadsheet, it transfers it into
specific
fields onto the first one. My problem comes in on the
1st
spreadsheet. The 1st spreadsheet will always remain
the
same. But the amount of information inputted into the
1st
spreadsheet is different everytime, and ranges from
5 -
20
things inputted. Right now I have set aside about 25
spaces on the 1st sheet, when the information is
entered
into the 2nd sheet, it is then displayed on the 1st
sheet,
but sometimes I have extra space on the first sheet
cause
the information inputted is less than the amount of
space
i've allocated for it. My question is this: is there
any
way that I can have a program in excel that would
hide a
row, if the value in a cell in that row equaled 0. If
anyone can help me with this it would be greatly
appreciated. Thanks


.



.

Nate,

Change

With ActiveCell.EntireColumn

to

With Range("B30:B59")

HTH,
Bernie
MS Excel MVP

wrote in message
...
Bernie,

It's almost perfect and I thank you very much for this.
The Macro will run everytime the sheet is opened, and the
data now appears when I input it. The last thing is this:
The Macro is going to be run on the same number of cells
every time. Where, in the code, can I enter the cells that
I want the macro to always be run on. The code will always
be run on B30 - B59 if that helps. Thanks a lot.

Nate

-----Original Message-----
Nate,

I think I understand your requirements....

Add this as the first line of the macro (below the dim
statements)

Cells.EntireRow.Hidden = False

then copy this event code, right-click on the sheet tab
of the sheet where
the rows need to be hidden, select "View Code", and paste
into the window
that appears. Then when you select that sheet, the macro
to hide the rows
will be run, and the hiding will be based on the updated
values.

Private Sub Worksheet_Activate()
HideZeroValueRows
End Sub

HTH,
Bernie
MS Excel MVP


wrote in message
...
Bernie,

This does work very well in hiding the rows with 0's. My
only problem is this. I'm run the macro which then hides
the rows. The problem is when I put information into the
2nd sheet, the values of the cells change from 0 to
whatever I input in, but the cells still stay hidden, is
there some way which I can run the macro, which will
hide
the cells(cause their value equals 0) and then when I
input information(changing their value from 0 to
something
else) and the cells would appear again, when it had
information in it? Is this possible? Thanks.

Nate

-----Original Message-----
Nate,

Select a cell in the column of interest, then run the
macro below.

HTH,
Bernie
MS Excel MVP

Sub HideZeroValueRows()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFind As Double
Dim firstAddress As String


myFind = 0
With ActiveCell.EntireColumn

Set c = .Find(myFind, LookIn:=xlValues,
lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address < firstAddress
Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
d.Select
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
'Hide the rows of all the found cells
d.EntireRow.Hidden = True
ActiveCell.Select

End Sub



"Nate" wrote in message
...
I didn't know you could program in Excel until I
found
this today. I have a problem with a spreadsheet i'm
trying
to make. Here's my problem.

Basically I have one spreadsheet which displays all
my
information, and another spreadsheet(different tab)
which
you input all your information. When you input
information
into the 2nd spreadsheet, it transfers it into
specific
fields onto the first one. My problem comes in on the
1st
spreadsheet. The 1st spreadsheet will always remain
the
same. But the amount of information inputted into the
1st
spreadsheet is different everytime, and ranges from
5 -
20
things inputted. Right now I have set aside about 25
spaces on the 1st sheet, when the information is
entered
into the 2nd sheet, it is then displayed on the 1st
sheet,
but sometimes I have extra space on the first sheet
cause
the information inputted is less than the amount of
space
i've allocated for it. My question is this: is there
any
way that I can have a program in excel that would
hide a
row, if the value in a cell in that row equaled 0. If
anyone can help me with this it would be greatly
appreciated. Thanks


.



.

Bernie,

Thanks for all your help, the program works great now.

Thanks again,

Nate

-----Original Message-----
Nate,

Change

With ActiveCell.EntireColumn

to

With Range("B30:B59")

HTH,
Bernie
MS Excel MVP

wrote in message
...
Bernie,

It's almost perfect and I thank you very much for this.
The Macro will run everytime the sheet is opened, and
the
data now appears when I input it. The last thing is
this:
The Macro is going to be run on the same number of cells
every time. Where, in the code, can I enter the cells
that
I want the macro to always be run on. The code will
always
be run on B30 - B59 if that helps. Thanks a lot.

Nate

-----Original Message-----
Nate,

I think I understand your requirements....

Add this as the first line of the macro (below the dim
statements)

Cells.EntireRow.Hidden = False

then copy this event code, right-click on the sheet tab
of the sheet where
the rows need to be hidden, select "View Code", and
paste
into the window
that appears. Then when you select that sheet, the
macro
to hide the rows
will be run, and the hiding will be based on the
updated
values.

Private Sub Worksheet_Activate()
HideZeroValueRows
End Sub

HTH,
Bernie
MS Excel MVP


wrote in message
...
Bernie,

This does work very well in hiding the rows with
0's. My
only problem is this. I'm run the macro which then
hides
the rows. The problem is when I put information into
the
2nd sheet, the values of the cells change from 0 to
whatever I input in, but the cells still stay
hidden, is
there some way which I can run the macro, which will
hide
the cells(cause their value equals 0) and then when I
input information(changing their value from 0 to
something
else) and the cells would appear again, when it had
information in it? Is this possible? Thanks.

Nate

-----Original Message-----
Nate,

Select a cell in the column of interest, then run
the
macro below.

HTH,
Bernie
MS Excel MVP

Sub HideZeroValueRows()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you
want
Dim myFind As Double
Dim firstAddress As String


myFind = 0
With ActiveCell.EntireColumn

Set c = .Find(myFind, LookIn:=xlValues,
lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <
firstAddress
Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
d.Select
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
'Hide the rows of all the found cells
d.EntireRow.Hidden = True
ActiveCell.Select

End Sub



"Nate" wrote in message
...
I didn't know you could program in Excel until I
found
this today. I have a problem with a spreadsheet
i'm
trying
to make. Here's my problem.

Basically I have one spreadsheet which displays
all
my
information, and another spreadsheet(different
tab)
which
you input all your information. When you input
information
into the 2nd spreadsheet, it transfers it into
specific
fields onto the first one. My problem comes in on
the
1st
spreadsheet. The 1st spreadsheet will always
remain
the
same. But the amount of information inputted into
the
1st
spreadsheet is different everytime, and ranges
from
5 -
20
things inputted. Right now I have set aside about
25
spaces on the 1st sheet, when the information is
entered
into the 2nd sheet, it is then displayed on the
1st
sheet,
but sometimes I have extra space on the first
sheet
cause
the information inputted is less than the amount
of
space
i've allocated for it. My question is this: is
there
any
way that I can have a program in excel that would
hide a
row, if the value in a cell in that row equaled
0. If
anyone can help me with this it would be greatly
appreciated. Thanks


.



.



.