Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Can you tell me how to solve the issue of not hiding the totally blank
rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Try this:
If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Not sure if you mean to add this statement to the beginning of the hiderows
dialog.....if I do it gives me an error. Did you mean something else? Sorry, not a very advanced VBE user. Thanks so much for your help!! Heidi "JLGWhiz" wrote: Try this: If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Scratch my first response.....If I add it into the existing hiderows dialog
to replace that one line it gives me the error "sub or function not defined" and the 'IsBlank' is highlighted. Does that help? Thanks! Heidi "JLGWhiz" wrote: Try this: If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Sorry, my mind went blank. <g
Change the IsBlank to IsEmpty "hko78" wrote: Scratch my first response.....If I add it into the existing hiderows dialog to replace that one line it gives me the error "sub or function not defined" and the 'IsBlank' is highlighted. Does that help? Thanks! Heidi "JLGWhiz" wrote: Try this: If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
No, that does not work either. It does not do anything when I try to run it.
Any other ideas? Thanks for trying!! Heidi "JLGWhiz" wrote: Sorry, my mind went blank. <g Change the IsBlank to IsEmpty "hko78" wrote: Scratch my first response.....If I add it into the existing hiderows dialog to replace that one line it gives me the error "sub or function not defined" and the 'IsBlank' is highlighted. Does that help? Thanks! Heidi "JLGWhiz" wrote: Try this: If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Let's try it this way:
If Cells(i, 3) < "" And Cells(i, 3) = 0 _ And Cells(i, 4) < "" And Cells(i, 4) = 0 Then The point is that Excel sees blank, "" and 0 as being equal, but VBA can tell the difference, so the statement has to be constructed so that it will execute only when it finds a zero and not if it find a null string or blank. There have been several postings on this but I did not copy them because I try to avoid the need for them. "hko78" wrote: No, that does not work either. It does not do anything when I try to run it. Any other ideas? Thanks for trying!! Heidi "JLGWhiz" wrote: Sorry, my mind went blank. <g Change the IsBlank to IsEmpty "hko78" wrote: Scratch my first response.....If I add it into the existing hiderows dialog to replace that one line it gives me the error "sub or function not defined" and the 'IsBlank' is highlighted. Does that help? Thanks! Heidi "JLGWhiz" wrote: Try this: If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Still nothing. I've looked at several other posting, but thought it better
to get a fresh solution because I couldn't find one that mirrored my case. Thanks!! "JLGWhiz" wrote: Let's try it this way: If Cells(i, 3) < "" And Cells(i, 3) = 0 _ And Cells(i, 4) < "" And Cells(i, 4) = 0 Then The point is that Excel sees blank, "" and 0 as being equal, but VBA can tell the difference, so the statement has to be constructed so that it will execute only when it finds a zero and not if it find a null string or blank. There have been several postings on this but I did not copy them because I try to avoid the need for them. "hko78" wrote: No, that does not work either. It does not do anything when I try to run it. Any other ideas? Thanks for trying!! Heidi "JLGWhiz" wrote: Sorry, my mind went blank. <g Change the IsBlank to IsEmpty "hko78" wrote: Scratch my first response.....If I add it into the existing hiderows dialog to replace that one line it gives me the error "sub or function not defined" and the 'IsBlank' is highlighted. Does that help? Thanks! Heidi "JLGWhiz" wrote: Try this: If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Last stab at this. It worked in my test once I used the right syntax. It
skipped the rows where the cell was blank but hid the rows where the cell contained zero. If IsEmpty(Cells(i, 3)) = False And Cells(i, 3) = 0 _ And IsEmpty(Cells(i, 4)) = False And Cells(i, 4) = 0 Then If it still don't work in your code, then I am out of suggestions. "hko78" wrote: Still nothing. I've looked at several other posting, but thought it better to get a fresh solution because I couldn't find one that mirrored my case. Thanks!! "JLGWhiz" wrote: Let's try it this way: If Cells(i, 3) < "" And Cells(i, 3) = 0 _ And Cells(i, 4) < "" And Cells(i, 4) = 0 Then The point is that Excel sees blank, "" and 0 as being equal, but VBA can tell the difference, so the statement has to be constructed so that it will execute only when it finds a zero and not if it find a null string or blank. There have been several postings on this but I did not copy them because I try to avoid the need for them. "hko78" wrote: No, that does not work either. It does not do anything when I try to run it. Any other ideas? Thanks for trying!! Heidi "JLGWhiz" wrote: Sorry, my mind went blank. <g Change the IsBlank to IsEmpty "hko78" wrote: Scratch my first response.....If I add it into the existing hiderows dialog to replace that one line it gives me the error "sub or function not defined" and the 'IsBlank' is highlighted. Does that help? Thanks! Heidi "JLGWhiz" wrote: Try this: If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Is this exactly how it should look when I use your suggestion below? I just
want to make sure. Thanks again for your assistance. I really appreciate it! Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If IsEmpty(Cells(i, 3)) = False And Cells(i, 3) = 0 _ And IsEmpty(Cells(i, 4)) = False And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub "JLGWhiz" wrote: Last stab at this. It worked in my test once I used the right syntax. It skipped the rows where the cell was blank but hid the rows where the cell contained zero. If IsEmpty(Cells(i, 3)) = False And Cells(i, 3) = 0 _ And IsEmpty(Cells(i, 4)) = False And Cells(i, 4) = 0 Then If it still don't work in your code, then I am out of suggestions. "hko78" wrote: Still nothing. I've looked at several other posting, but thought it better to get a fresh solution because I couldn't find one that mirrored my case. Thanks!! "JLGWhiz" wrote: Let's try it this way: If Cells(i, 3) < "" And Cells(i, 3) = 0 _ And Cells(i, 4) < "" And Cells(i, 4) = 0 Then The point is that Excel sees blank, "" and 0 as being equal, but VBA can tell the difference, so the statement has to be constructed so that it will execute only when it finds a zero and not if it find a null string or blank. There have been several postings on this but I did not copy them because I try to avoid the need for them. "hko78" wrote: No, that does not work either. It does not do anything when I try to run it. Any other ideas? Thanks for trying!! Heidi "JLGWhiz" wrote: Sorry, my mind went blank. <g Change the IsBlank to IsEmpty "hko78" wrote: Scratch my first response.....If I add it into the existing hiderows dialog to replace that one line it gives me the error "sub or function not defined" and the 'IsBlank' is highlighted. Does that help? Thanks! Heidi "JLGWhiz" wrote: Try this: If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide rows, but not blank rows used for formatting
Just wanted to let you know that this is what finally worked, changing the
range to suit my worksheet. Thanks for all your help though!! Heidi Sub HideRows() ActiveWindow.DisplayZeros = True On Error Resume Next Application.ScreenUpdating = False With Range("e13:i600") For i = 1 To .Rows.Count If WorksheetFunction.CountA(.Rows(i)) 0 Then If WorksheetFunction.Sum(.Rows(i)) = 0 Then .Rows(i).EntireRow.Hidden = True Else .Rows(i).EntireRow.Hidden = False End If End If Next i End With Application.ScreenUpdating = True End Sub -- "hko78" wrote: Is this exactly how it should look when I use your suggestion below? I just want to make sure. Thanks again for your assistance. I really appreciate it! Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If IsEmpty(Cells(i, 3)) = False And Cells(i, 3) = 0 _ And IsEmpty(Cells(i, 4)) = False And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub "JLGWhiz" wrote: Last stab at this. It worked in my test once I used the right syntax. It skipped the rows where the cell was blank but hid the rows where the cell contained zero. If IsEmpty(Cells(i, 3)) = False And Cells(i, 3) = 0 _ And IsEmpty(Cells(i, 4)) = False And Cells(i, 4) = 0 Then If it still don't work in your code, then I am out of suggestions. "hko78" wrote: Still nothing. I've looked at several other posting, but thought it better to get a fresh solution because I couldn't find one that mirrored my case. Thanks!! "JLGWhiz" wrote: Let's try it this way: If Cells(i, 3) < "" And Cells(i, 3) = 0 _ And Cells(i, 4) < "" And Cells(i, 4) = 0 Then The point is that Excel sees blank, "" and 0 as being equal, but VBA can tell the difference, so the statement has to be constructed so that it will execute only when it finds a zero and not if it find a null string or blank. There have been several postings on this but I did not copy them because I try to avoid the need for them. "hko78" wrote: No, that does not work either. It does not do anything when I try to run it. Any other ideas? Thanks for trying!! Heidi "JLGWhiz" wrote: Sorry, my mind went blank. <g Change the IsBlank to IsEmpty "hko78" wrote: Scratch my first response.....If I add it into the existing hiderows dialog to replace that one line it gives me the error "sub or function not defined" and the 'IsBlank' is highlighted. Does that help? Thanks! Heidi "JLGWhiz" wrote: Try this: If Not IsBlank(Cells(i, 3)) And Cells(i, 3) = 0 _ And Not IsBlank(Cells(i, 4)) And Cells(i, 4) = 0 Then "hko78" wrote: Can you tell me how to solve the issue of not hiding the totally blank rows that are part of the worksheet for formatting purposes, but do hide the rows that have zeros in them based on formulas. I added the hiderows/showrows to VBE, but it removes all the zero and blank rows. Sub Hiderows() Dim lr As Long, i As Long With ActiveSheet lr = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lr If Cells(i, 3) = 0 And Cells(i, 4) = 0 Then Cells(i, 1).EntireRow.Hidden = True End If Next End With End Sub I would be most appreciative for a fast solution. Thanks! Heidi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Blank Rows | Excel Worksheet Functions | |||
Hide Zero Rows - But Not Blank Rows | Excel Programming | |||
Macro to hide blank rows | Excel Programming | |||
hide blank rows | Excel Programming | |||
Hide Blank Rows | Excel Worksheet Functions |