Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
<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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
yeah, what happens is the data from that field is on another sheet...field B5
checks that other sheets to see if it =$0.00, then B6 checks is B5 "" B7 checks if B5"" and so on. I know there are quicker ways to populate it, but I'm a bit oldschool Quattro...which is why I am learning this stuff to make Excel better than my other sheets used to be. :) So if I change your code to read B it should work on my original cell reference for ""? "Rick Rothstein (MVP - VB)" wrote: 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
that works nicely when I made it B for my main reference cell...thank you so
much. thanks to the other postings as well, just so new to coding Excel that I probably bugged the coding. ;) "Dgephri" wrote: yeah, what happens is the data from that field is on another sheet...field B5 checks that other sheets to see if it =$0.00, then B6 checks is B5 "" B7 checks if B5"" and so on. I know there are quicker ways to populate it, but I'm a bit oldschool Quattro...which is why I am learning this stuff to make Excel better than my other sheets used to be. :) So if I change your code to read B it should work on my original cell reference for ""? "Rick Rothstein (MVP - VB)" wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
yeah, what happens is the data from that field is on another sheet...field
B5 checks that other sheets to see if it =$0.00, then B6 checks is B5 "" B7 checks if B5"" and so on. I know there are quicker ways to populate it, but I'm a bit oldschool Quattro...which is why I am learning this stuff to make Excel better than my other sheets used to be. :) So if I change your code to read B it should work on my original cell reference for ""? I'm still a little confused. Are you saying that ONE SINGLE cell (B5) in the other sheet controls whether EVERY row in your data on the current sheet is displayed or not? Every row??? If that is not what you are saying, then you will need to tell us how to identify which rows of data on the current sheet are linked into B5 on the other sheet and which are not. Rick |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
My last post crossed your last post... as it seems you have solved your
problem, you can ignore my last message. Rick "Rick Rothstein (MVP - VB)" wrote in message ... yeah, what happens is the data from that field is on another sheet...field B5 checks that other sheets to see if it =$0.00, then B6 checks is B5 "" B7 checks if B5"" and so on. I know there are quicker ways to populate it, but I'm a bit oldschool Quattro...which is why I am learning this stuff to make Excel better than my other sheets used to be. :) So if I change your code to read B it should work on my original cell reference for ""? I'm still a little confused. Are you saying that ONE SINGLE cell (B5) in the other sheet controls whether EVERY row in your data on the current sheet is displayed or not? Every row??? If that is not what you are saying, then you will need to tell us how to identify which rows of data on the current sheet are linked into B5 on the other sheet and which are not. Rick |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I hide rows when a field is blank?
sorry to confuse: it controls the other fields in that row only, B6 controls
row 6 population, B7 controls row 7 population, etc. your code works nicely with B, and I even made an "unhide" variant to reverse it if needed by changing the final conditional to "If .Cells(X, "B").Value = 0 Or .Cells(X, "B").Value = "" Then .Cells(X, "B").EntireRow.Hidden = False" works wonderfully now to Hide and UnHide, many thanks! "Rick Rothstein (MVP - VB)" wrote: yeah, what happens is the data from that field is on another sheet...field B5 checks that other sheets to see if it =$0.00, then B6 checks is B5 "" B7 checks if B5"" and so on. I know there are quicker ways to populate it, but I'm a bit oldschool Quattro...which is why I am learning this stuff to make Excel better than my other sheets used to be. :) So if I change your code to read B it should work on my original cell reference for ""? I'm still a little confused. Are you saying that ONE SINGLE cell (B5) in the other sheet controls whether EVERY row in your data on the current sheet is displayed or not? Every row??? If that is not what you are saying, then you will need to tell us how to identify which rows of data on the current sheet are linked into B5 on the other sheet and which are not. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto hide blank rows | Excel Discussion (Misc queries) | |||
Hide Blank Rows | Excel Worksheet Functions | |||
hide blank rows | Excel Programming | |||
Hide Blank Rows | Excel Worksheet Functions | |||
Hide blank rows in a range | Excel Programming |