Programming Help
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
.
.
.
|