Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows only when a condition has been met
I am trying to come up with something that will look at rows 3 through 2800
and hide any row where the value in column g (of the respective row) was zero. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows only when a condition has been met
Give this code a try...
Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long LastRow = Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End Sub Rick "ordnance1" wrote in message ... I am trying to come up with something that will look at rows 3 through 2800 and hide any row where the value in column g (of the respective row) was zero. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows only when a condition has been met
Nothing happens when I run this code.
"Rick Rothstein (MVP - VB)" wrote in message ... Give this code a try... Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long LastRow = Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End Sub Rick "ordnance1" wrote in message ... I am trying to come up with something that will look at rows 3 through 2800 and hide any row where the value in column g (of the respective row) was zero. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows only when a condition has been met
Where are you running it from? It was designed to be placed in the code
window for the worksheet you want it to apply to. So, if Sheet3 is the worksheet you want to hide the rows in, right-click the name tab for that worksheet, select View Code from the menu that pops up, and copy/paste the code into the code window that appeared. Then, go to Sheet3 and press Alt+F8, select HideRowIfZeroInG from the list and click the Run button. If you would like to keep the code in some different location, then we will have to incorporate a reference to the sheet name within the code itself. The following modification to what I posted earlier will allow that... Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long With Worksheets("Sheet3") LastRow = .Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In .Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End With End Sub Note: Change the reference to Sheet3 (keep the quote marks) in the With statement to the actual sheet name you want to hide the rows on. Rick "Patrick C. Simonds" wrote in message ... Nothing happens when I run this code. "Rick Rothstein (MVP - VB)" wrote in message ... Give this code a try... Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long LastRow = Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End Sub Rick "ordnance1" wrote in message ... I am trying to come up with something that will look at rows 3 through 2800 and hide any row where the value in column g (of the respective row) was zero. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows only when a condition has been met
Thank you very much. I totally over looked to reference to sheet3
"Rick Rothstein (MVP - VB)" wrote in message ... Where are you running it from? It was designed to be placed in the code window for the worksheet you want it to apply to. So, if Sheet3 is the worksheet you want to hide the rows in, right-click the name tab for that worksheet, select View Code from the menu that pops up, and copy/paste the code into the code window that appeared. Then, go to Sheet3 and press Alt+F8, select HideRowIfZeroInG from the list and click the Run button. If you would like to keep the code in some different location, then we will have to incorporate a reference to the sheet name within the code itself. The following modification to what I posted earlier will allow that... Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long With Worksheets("Sheet3") LastRow = .Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In .Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End With End Sub Note: Change the reference to Sheet3 (keep the quote marks) in the With statement to the actual sheet name you want to hide the rows on. Rick "Patrick C. Simonds" wrote in message ... Nothing happens when I run this code. "Rick Rothstein (MVP - VB)" wrote in message ... Give this code a try... Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long LastRow = Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End Sub Rick "ordnance1" wrote in message ... I am trying to come up with something that will look at rows 3 through 2800 and hide any row where the value in column g (of the respective row) was zero. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows only when a condition has been met
Hi Rick
I tried to use the code and couldn't get it to work ... until ... I either removed the 'And' portion in the following line or changed the 'And' to 'Or': If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Isn't the line as originally written attempting to hide the row if the value = 0 AND if the value isn't blank? So no rows will ever be hidden? Just curious and thanks for all your help Rick. Brett "Rick Rothstein (MVP - VB)" wrote: Where are you running it from? It was designed to be placed in the code window for the worksheet you want it to apply to. So, if Sheet3 is the worksheet you want to hide the rows in, right-click the name tab for that worksheet, select View Code from the menu that pops up, and copy/paste the code into the code window that appeared. Then, go to Sheet3 and press Alt+F8, select HideRowIfZeroInG from the list and click the Run button. If you would like to keep the code in some different location, then we will have to incorporate a reference to the sheet name within the code itself. The following modification to what I posted earlier will allow that... Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long With Worksheets("Sheet3") LastRow = .Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In .Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End With End Sub Note: Change the reference to Sheet3 (keep the quote marks) in the With statement to the actual sheet name you want to hide the rows on. Rick "Patrick C. Simonds" wrote in message ... Nothing happens when I run this code. "Rick Rothstein (MVP - VB)" wrote in message ... Give this code a try... Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long LastRow = Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End Sub Rick "ordnance1" wrote in message ... I am trying to come up with something that will look at rows 3 through 2800 and hide any row where the value in column g (of the respective row) was zero. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows only when a condition has been met
Hi Rick
As a follow-up ... I'd like to be able to unhide these specific rows as well. I used your original code to do this but it does it line by line - not very fast. Is there a simpler [quicker] macro that can unhide a set range of rows? In my case it's row 85-136. Thanks for your help Rick. B "Rick Rothstein (MVP - VB)" wrote: Where are you running it from? It was designed to be placed in the code window for the worksheet you want it to apply to. So, if Sheet3 is the worksheet you want to hide the rows in, right-click the name tab for that worksheet, select View Code from the menu that pops up, and copy/paste the code into the code window that appeared. Then, go to Sheet3 and press Alt+F8, select HideRowIfZeroInG from the list and click the Run button. If you would like to keep the code in some different location, then we will have to incorporate a reference to the sheet name within the code itself. The following modification to what I posted earlier will allow that... Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long With Worksheets("Sheet3") LastRow = .Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In .Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End With End Sub Note: Change the reference to Sheet3 (keep the quote marks) in the With statement to the actual sheet name you want to hide the rows on. Rick "Patrick C. Simonds" wrote in message ... Nothing happens when I run this code. "Rick Rothstein (MVP - VB)" wrote in message ... Give this code a try... Sub HideRowIfZeroInG() Dim R As Range Dim LastRow As Long LastRow = Cells(Rows.Count, "G").End(xlUp).Row If LastRow 2800 Then LastRow = 2800 For Each R In Range("G3:G" & CStr(LastRow)) If R.Value = 0 And R.Value < "" Then R.EntireRow.Hidden = True Next End Sub Rick "ordnance1" wrote in message ... I am trying to come up with something that will look at rows 3 through 2800 and hide any row where the value in column g (of the respective row) was zero. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color alternate rows when after hiding selected rows | Excel Worksheet Functions | |||
Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
Hiding rows on condition | Excel Worksheet Functions | |||
Hiding cells on condition | Excel Worksheet Functions |