A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to hide rows, but not blank rows used for formatting



 
 
Thread Tools Display Modes
  #1  
Old May 13th 08, 08:58 PM posted to microsoft.public.excel.programming
hko78
external usenet poster
 
Posts: 8
Default 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


Ads
  #2  
Old May 13th 08, 09:13 PM posted to microsoft.public.excel.programming
JLGWhiz
external usenet poster
 
Posts: 3,986
Default 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  
Old May 13th 08, 09:31 PM posted to microsoft.public.excel.programming
hko78
external usenet poster
 
Posts: 8
Default 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  
Old May 13th 08, 09:41 PM posted to microsoft.public.excel.programming
hko78
external usenet poster
 
Posts: 8
Default 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  
Old May 13th 08, 09:45 PM posted to microsoft.public.excel.programming
JLGWhiz
external usenet poster
 
Posts: 3,986
Default 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  
Old May 13th 08, 09:58 PM posted to microsoft.public.excel.programming
hko78
external usenet poster
 
Posts: 8
Default 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  
Old May 13th 08, 10:13 PM posted to microsoft.public.excel.programming
JLGWhiz
external usenet poster
 
Posts: 3,986
Default 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  
Old May 13th 08, 10:27 PM posted to microsoft.public.excel.programming
hko78
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
> > > > > >
> > > > > >

  #9  
Old May 13th 08, 11:56 PM posted to microsoft.public.excel.programming
JLGWhiz
external usenet poster
 
Posts: 3,986
Default 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  
Old May 14th 08, 03:56 PM posted to microsoft.public.excel.programming
hko78
external usenet poster
 
Posts: 8
Default 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
> > > > > > > >
> > > > > > > >

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide Blank Rows TamIam Excel Worksheet Functions 3 May 6th 09 08:42 PM
Hide Zero Rows - But Not Blank Rows MikeF[_2_] Excel Programming 3 May 13th 08 03:40 PM
Macro to hide blank rows Alan Smith Excel Programming 3 February 23rd 07 04:35 PM
hide blank rows violet Excel Programming 3 November 9th 06 09:20 AM
Hide Blank Rows dee Excel Worksheet Functions 2 May 17th 06 11:30 PM


All times are GMT +1. The time now is 05:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.