Programming Help

N

Nate

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
 
B

Bernie Deitrick

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
 
G

Guest

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
 
B

Bernie Deitrick

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
 
G

Guest

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
 
B

Bernie Deitrick

Nate,

Change

With ActiveCell.EntireColumn

to

With Range("B30:B59")

HTH,
Bernie
MS Excel MVP
 
Top