Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 173
Default Hide Zero Rows - But Not Blank Rows

Hello,

Need to hide rows that a certain range sums to zero in, which the code below
does.
What it also does is hide any blank rows as well, which does not work for
the intended purpose.

Tried numerous attempts at [paraphrasing] ....

If row = 0 hide AND If row = "" do not hide

.... To no avail.

Thanx in advance to anyone who can solve this.

- Mike


Sub HideRows()
ActiveWindow.DisplayZeros = True

On Error Resume Next
With Range("e13:i600")
..EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
..Rows(i).EntireRow.Hidden = True
End If
Next i
End With

On Error Resume Next
With Range("e13:i600")
..EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = "" Then
..Rows(i).EntireRow.Hidden = False
End If
Next i
End With

End Sub


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,069
Default Hide Zero Rows - But Not Blank Rows

probably a better way to do this but adapting your code I think this may do
what you want:

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
--
JB


"MikeF" wrote:

Hello,

Need to hide rows that a certain range sums to zero in, which the code below
does.
What it also does is hide any blank rows as well, which does not work for
the intended purpose.

Tried numerous attempts at [paraphrasing] ....

If row = 0 hide AND If row = "" do not hide

... To no avail.

Thanx in advance to anyone who can solve this.

- Mike


Sub HideRows()
ActiveWindow.DisplayZeros = True

On Error Resume Next
With Range("e13:i600")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With

On Error Resume Next
With Range("e13:i600")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = "" Then
.Rows(i).EntireRow.Hidden = False
End If
Next i
End With

End Sub


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 173
Default Hide Zero Rows - But Not Blank Rows


John,

Thank you.
This worked.

But I think you're right, there MUST be a better way to achieve these results.

Although the sheet doesn't lend well to Advanced Filter, re non-consistent
field-names, shoudn't there still be a "standard" piece of code that says
"look at range nn:nn, and if the sum of x thru y cells = 0, hide the entire
row, and disregard any cells the sum to null" ??

But needed a quick fix, and you certainly came through.
Thanx again.

- Mike

"MikeF" wrote:

Hello,

Need to hide rows that a certain range sums to zero in, which the code below
does.
What it also does is hide any blank rows as well, which does not work for
the intended purpose.

Tried numerous attempts at [paraphrasing] ....

If row = 0 hide AND If row = "" do not hide

... To no avail.

Thanx in advance to anyone who can solve this.

- Mike


Sub HideRows()
ActiveWindow.DisplayZeros = True

On Error Resume Next
With Range("e13:i600")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With

On Error Resume Next
With Range("e13:i600")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = "" Then
.Rows(i).EntireRow.Hidden = False
End If
Next i
End With

End Sub


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Hide Zero Rows - But Not Blank Rows

Can you tell me how to solve the same issue of not hiding the totally blank
rows but using the hiderows vbe as follows:

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!

Thanks!

Heidi

"MikeF" wrote:

Hello,

Need to hide rows that a certain range sums to zero in, which the code below
does.
What it also does is hide any blank rows as well, which does not work for
the intended purpose.

Tried numerous attempts at [paraphrasing] ....

If row = 0 hide AND If row = "" do not hide

... To no avail.

Thanx in advance to anyone who can solve this.

- Mike


Sub HideRows()
ActiveWindow.DisplayZeros = True

On Error Resume Next
With Range("e13:i600")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With

On Error Resume Next
With Range("e13:i600")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = "" Then
.Rows(i).EntireRow.Hidden = False
End If
Next i
End With

End Sub


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
auto hide blank rows S A Jameel Excel Discussion (Misc queries) 1 July 12th 09 03:36 PM
Macro to HIDE blank rows JForsyth Excel Worksheet Functions 6 June 5th 09 04:51 PM
Hide Blank Rows TamIam Excel Worksheet Functions 3 May 6th 09 08:42 PM
hide blank rows violet Excel Programming 3 November 9th 06 10: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 04:59 PM.

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

About Us

"It's about Microsoft Excel"