View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
hko78 hko78 is offline
external usenet poster
 
Posts: 8
Default 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