Hi,

I was wondering if someone can help me out on this topic of mine or at least
give their opinion.

I have seen all of these macros that are able to help people hide rows of
zeroes or other values. But is it possible to create a GENERIC macro that can
be used to hide rows of zeroes or other values in other spreadsheets? I see
this as being a difficult task because the macro will need to be able to
adjust to the different number of rows and columns in different spreadsheets.
Hence, this macro will need a way to determine the size of the data (number
of rows and columns), and I'm not sure how to do that in VBA.

I already tried using a filter, but there are flaws to it because it can
still hide rows that don't have zeroes.

If anyone can help me out or share their opinions on this topic, that would
be great!


-Daniel

Daniel,

You can try the following code. The parameters are as follows:
WithValue - If a cell has this value, that row is hidden.
InColumn - The column number or letter indicating which column to test
with WithValue.
OnSheet - a reference to the worksheet to use. If omitted, the
ActiveSheet is used.
EmptyEqualsZero - Indicates that an empty cell is to be treated as
having a value of 0. If WithValue is 0 and EmptyEqualZero is True, then
both an empty cell and 0 value cell will be hidden. If WithValue is
0 and EmptyEqualZero is False, an empty cell does not cause the row to be
hidden.

You can call this code from VBA with

HideRow("A","B",WorkSheets("Sheet1"),True

This will look on Sheet1 in column B for the value "A" and hide those rows
in which an "A" was found in column B.


Sub HideRows(WithValue As Variant, InColumn As Variant, _
Optional OnSheet As Worksheet, Optional EmptyEqualsZero As Boolean =
False)

Dim WS As Worksheet
Dim StartRow As Long
Dim EndRow As Long
Dim R As Range
Dim N As Long
Dim C As Long

If OnSheet Is Nothing Then
Set WS = ActiveSheet
Else
Set WS = OnSheet
End If

If IsNumeric(InColumn) Then
C = InColumn
Else
On Error Resume Next
C = WS.Cells(1, InColumn).Column
If Err.Number < 0 Then
Exit Sub
End If
End If

If C <= 0 Or C = WS.Rows.Count Then
Exit Sub
End If

Set R = WS.Cells(1, InColumn)
Do Until R.Value = WithValue
If EmptyEqualsZero = True Then
If WithValue = 0 Then
If R.Value = 0 Then
Exit Do
End If
End If
End If
Set R = R(2, 1)
If R.Row = WS.Rows.Count Then
Exit Sub
End If
Loop
StartRow = R.Row

With WS.UsedRange
Set R = .Cells(.Cells.Count)
End With
If R.Row = 1 Then
Exit Sub
End If
Do Until R.Value = WithValue
Set R = R(0, 1)
If R.Row = 1 Then
Exit Sub
End If
Loop
EndRow = R.Row

With WS
For N = EndRow To StartRow Step -1
If .Cells(N, InColumn).Value = vbNullString Then
If EmptyEqualsZero = True Then
If WithValue = 0 Then
.Rows(N).Hidden = True
End If
End If
Else
If .Cells(N, InColumn).Value = WithValue Then
.Rows(N).Hidden = True
End If
End If
Next N
End With

End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Daniel" wrote in message
...
Hi,

I was wondering if someone can help me out on this topic of mine or at
least
give their opinion.

I have seen all of these macros that are able to help people hide rows of
zeroes or other values. But is it possible to create a GENERIC macro that
can
be used to hide rows of zeroes or other values in other spreadsheets? I
see
this as being a difficult task because the macro will need to be able to
adjust to the different number of rows and columns in different
spreadsheets.
Hence, this macro will need a way to determine the size of the data
(number
of rows and columns), and I'm not sure how to do that in VBA.

I already tried using a filter, but there are flaws to it because it can
still hide rows that don't have zeroes.

If anyone can help me out or share their opinions on this topic, that
would
be great!


-Daniel