How can I hide Row 5 when Cell B5 is blank, and likewise for all rows in the
table? Tried pasting from another thread here into the VB macro page, and I
must have missed something in the process.

General layout:
B5 checks if loan balance 0, then populates B5 with Borrower name. Other B
row cells all check if B5"" and then populate their functions.
But since loans get paid off, I don't want my front master sheet to get
overrun with all the old information, even though I don't want to delete the
history there...

Much thanks from a long time user of another spreadsheet, changing old stuff
over to Excel.

On Apr 11, 2:10*pm, Dgephri wrote:
How can I hide Row 5 when Cell B5 is blank, and likewise for all rows in the
table? *Tried pasting from another thread here into the VB macro page, and I
must have missed something in the process.

General layout:
B5 checks if loan balance 0, then populates B5 with Borrower name. *Other B
row cells all check if B5"" and then populate their functions.
But since loans get paid off, I don't want my front master sheet to get
overrun with all the old information, even though I don't want to delete the
history there...

Much thanks from a long time user of another spreadsheet, changing old stuff
over to Excel.

If the cells are based on formulas that checks the balance and returns
the formula if it's greater than zero and "" if not.

If they are just values you can do conditional formating to change the
forground and background colors to white as required.

HTH

Tim Mills-Groninger

Hi

Try this code. As it's a event macro it has to be pasted to the code
sheet for your master sheet. To open that code sheet, right click on
the tab for the mastersheet and select "View Code".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("B5") Then
If Range("B5").Value = "" Then Target.EntireRow.Hidden = True
End If
End Sub

Regards,
Per

On 11 Apr., 21:10, Dgephri wrote:
How can I hide Row 5 when Cell B5 is blank, and likewise for all rows in the
table? *Tried pasting from another thread here into the VB macro page, and I
must have missed something in the process.

General layout:
B5 checks if loan balance 0, then populates B5 with Borrower name. *Other B
row cells all check if B5"" and then populate their functions.
But since loans get paid off, I don't want my front master sheet to get
overrun with all the old information, even though I don't want to delete the
history there...

Much thanks from a long time user of another spreadsheet, changing old stuff
over to Excel.

sorry, new to much of this coding, but playing around, trying this:
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("B5") Then
If Range("B5").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B6") Then
If Range("B6").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B7") Then
If Range("B7").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B8") Then
If Range("B8").Value = "" Then Target.EntireRow.Hidden = True
End If
End Sub"
makes all rows disappear when I click on a cell. Was just curious if I can
target only the rows with relevant data or not. I can't even use "undo" with
this.

"Per Jessen" wrote:

Hi

Try this code. As it's a event macro it has to be pasted to the code
sheet for your master sheet. To open that code sheet, right click on
the tab for the mastersheet and select "View Code".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("B5") Then
If Range("B5").Value = "" Then Target.EntireRow.Hidden = True
End If
End Sub

Regards,
Per

On 11 Apr., 21:10, Dgephri wrote:
How can I hide Row 5 when Cell B5 is blank, and likewise for all rows in the
table? Tried pasting from another thread here into the VB macro page, and I
must have missed something in the process.

General layout:
B5 checks if loan balance 0, then populates B5 with Borrower name. Other B
row cells all check if B5"" and then populate their functions.
But since loans get paid off, I don't want my front master sheet to get
overrun with all the old information, even though I don't want to delete the
history there...

Much thanks from a long time user of another spreadsheet, changing old stuff
over to Excel.

Describe what makes up a row with relevant data on it.

Rick

"Dgephri" wrote in message
...
sorry, new to much of this coding, but playing around, trying this:
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("B5") Then
If Range("B5").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B6") Then
If Range("B6").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B7") Then
If Range("B7").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B8") Then
If Range("B8").Value = "" Then Target.EntireRow.Hidden = True
End If
End Sub"
makes all rows disappear when I click on a cell. Was just curious if I
can
target only the rows with relevant data or not. I can't even use "undo"
with
this.

"Per Jessen" wrote:

Hi

Try this code. As it's a event macro it has to be pasted to the code
sheet for your master sheet. To open that code sheet, right click on
the tab for the mastersheet and select "View Code".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("B5") Then
If Range("B5").Value = "" Then Target.EntireRow.Hidden = True
End If
End Sub

Regards,
Per

On 11 Apr., 21:10, Dgephri wrote:
How can I hide Row 5 when Cell B5 is blank, and likewise for all rows
in the
table? Tried pasting from another thread here into the VB macro page,
and I
must have missed something in the process.

General layout:
B5 checks if loan balance 0, then populates B5 with Borrower name.
Other B
row cells all check if B5"" and then populate their functions.
But since loans get paid off, I don't want my front master sheet to get
overrun with all the old information, even though I don't want to
delete the
history there...

Much thanks from a long time user of another spreadsheet, changing old
stuff
over to Excel.

<3255 NE 79th<10.75%<5/31/2008<$64,509.53<5/1/2008<$2,933.76
< for separation only.
But when the fourth field (64,509.53) is blank or 0.00 (loan paid off) I
would like to hide the entire row.

Trying to not to confuse the issue with data structure, but pasting those
codes into the "View Code" doesn't seem to do anything to Row B5 even when I
delete the contents (formula checking if Balance is 0.00).

The other code posted by PerJessen actually hides rows, but whatever row I
click on, not just blank ones...

"Rick Rothstein (MVP - VB)" wrote:

Describe what makes up a row with relevant data on it.

Rick

"Dgephri" wrote in message
...
sorry, new to much of this coding, but playing around, trying this:
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("B5") Then
If Range("B5").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B6") Then
If Range("B6").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B7") Then
If Range("B7").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B8") Then
If Range("B8").Value = "" Then Target.EntireRow.Hidden = True
End If
End Sub"
makes all rows disappear when I click on a cell. Was just curious if I
can
target only the rows with relevant data or not. I can't even use "undo"
with
this.

"Per Jessen" wrote:

Hi

Try this code. As it's a event macro it has to be pasted to the code
sheet for your master sheet. To open that code sheet, right click on
the tab for the mastersheet and select "View Code".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("B5") Then
If Range("B5").Value = "" Then Target.EntireRow.Hidden = True
End If
End Sub

Regards,
Per

On 11 Apr., 21:10, Dgephri wrote:
How can I hide Row 5 when Cell B5 is blank, and likewise for all rows
in the
table? Tried pasting from another thread here into the VB macro page,
and I
must have missed something in the process.

General layout:
B5 checks if loan balance 0, then populates B5 with Borrower name.
Other B
row cells all check if B5"" and then populate their functions.
But since loans get paid off, I don't want my front master sheet to get
overrun with all the old information, even though I don't want to
delete the
history there...

Much thanks from a long time user of another spreadsheet, changing old
stuff
over to Excel.

I'm a little confused between what you posted in your first message (looking
for B5 to be empty) and this message (4th field on the row to be empty).
Assuming your data starts in Column A (so that 4th column is Column D),
running this macro should do what you asked...

Sub HideRowsIfColumnDisEmpty()
Dim X As Long
Dim LastRowOfData As Long
With Worksheets("Sheet1")
LastRowOfData = .Cells(.Rows.Count, "D").End(xlUp).Row
For X = 1 To LastRowOfData
If .Cells(X, "D").Value = 0 Or .Cells(X, "D").Value = "" Then
.Cells(X, "D").EntireRow.Hidden = True
End If
Next
End With
End Sub

Make sure you change the Worksheets("Sheet1") reference to the actual sheet
name that your data is on.

Rick


"Dgephri" wrote in message
...
<3255 NE 79th<10.75%<5/31/2008<$64,509.53<5/1/2008<$2,933.76
< for separation only.
But when the fourth field (64,509.53) is blank or 0.00 (loan paid off) I
would like to hide the entire row.

Trying to not to confuse the issue with data structure, but pasting those
codes into the "View Code" doesn't seem to do anything to Row B5 even when
I
delete the contents (formula checking if Balance is 0.00).

The other code posted by PerJessen actually hides rows, but whatever row I
click on, not just blank ones...

"Rick Rothstein (MVP - VB)" wrote:

Describe what makes up a row with relevant data on it.

Rick

"Dgephri" wrote in message
...
sorry, new to much of this coding, but playing around, trying this:
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("B5") Then
If Range("B5").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B6") Then
If Range("B6").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B7") Then
If Range("B7").Value = "" Then Target.EntireRow.Hidden = True
End If
If Target = Range("B8") Then
If Range("B8").Value = "" Then Target.EntireRow.Hidden = True
End If
End Sub"
makes all rows disappear when I click on a cell. Was just curious if I
can
target only the rows with relevant data or not. I can't even use
"undo"
with
this.

"Per Jessen" wrote:

Hi

Try this code. As it's a event macro it has to be pasted to the code
sheet for your master sheet. To open that code sheet, right click on
the tab for the mastersheet and select "View Code".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = Range("B5") Then
If Range("B5").Value = "" Then Target.EntireRow.Hidden = True
End If
End Sub

Regards,
Per

On 11 Apr., 21:10, Dgephri wrote:
How can I hide Row 5 when Cell B5 is blank, and likewise for all
rows
in the
table? Tried pasting from another thread here into the VB macro
page,
and I
must have missed something in the process.

General layout:
B5 checks if loan balance 0, then populates B5 with Borrower name.
Other B
row cells all check if B5"" and then populate their functions.
But since loans get paid off, I don't want my front master sheet to
get
overrun with all the old information, even though I don't want to
delete the
history there...

Much thanks from a long time user of another spreadsheet, changing
old
stuff
over to Excel.

To hide Row 5 (on Sheet1) when B5 is blank (that is, when it has nothing in
it, not simply when it is zero)...

With Worksheets("Sheet1")
If .Cells(5, "C").Value = "" Then .Cells(5, "C").EntireRow.Hidden = True
End With

You can run a loop for the rows of interest substituting your loop index
counter in for the hard-coded 5's used above.

Rick


"Dgephri" wrote in message
...
How can I hide Row 5 when Cell B5 is blank, and likewise for all rows in
the
table? Tried pasting from another thread here into the VB macro page, and
I
must have missed something in the process.

General layout:
B5 checks if loan balance 0, then populates B5 with Borrower name. Other
B
row cells all check if B5"" and then populate their functions.
But since loans get paid off, I don't want my front master sheet to get
overrun with all the old information, even though I don't want to delete
the
history there...

Much thanks from a long time user of another spreadsheet, changing old
stuff
over to Excel.

looks simple enough, remaining questions:
1. new to loop index counters. Can I nest them for certain Row Ranges
(B...F, H...L, M...Q, etc.?)
2. do I just paste this in as a VB macro, save the function, and make it
work on startup?

"Rick Rothstein (MVP - VB)" wrote:

To hide Row 5 (on Sheet1) when B5 is blank (that is, when it has nothing in
it, not simply when it is zero)...

With Worksheets("Sheet1")
If .Cells(5, "C").Value = "" Then .Cells(5, "C").EntireRow.Hidden = True
End With

You can run a loop for the rows of interest substituting your loop index
counter in for the hard-coded 5's used above.

Rick


"Dgephri" wrote in message
...
How can I hide Row 5 when Cell B5 is blank, and likewise for all rows in
the
table? Tried pasting from another thread here into the VB macro page, and
I
must have missed something in the process.

General layout:
B5 checks if loan balance 0, then populates B5 with Borrower name. Other
B
row cells all check if B5"" and then populate their functions.
But since loans get paid off, I don't want my front master sheet to get
overrun with all the old information, even though I don't want to delete
the
history there...

Much thanks from a long time user of another spreadsheet, changing old
stuff
over to Excel.