Passing formula value to variable, hide rows
 

I'm creating a macro that will check each row to determine the values
of three separate columns. If any of those three values are something
other than zero, then the macro will not Hide the row. Otherwise, if
all three values are zero, then the macro will hide the row.

The catch to this one is that 1) the cells we're checking are
formula-driven, and 2) the cell values that we're testing are typically
displayed in percentage terms. I'm having difficulty getting the value
to pass through the testing subroutine. I'm not sure if I should be
trying to convert the formula value to text first, and then test it, or
if there's a better way.

Here is a sample of a couple of different ways I've tried this:

----------------------
First Attempt


Dim TestRows As Integer
Dim count As Integer

For TestRows = 18 To 25
count = 0
If cells(testrows, 3).value < 0 then count = count +1
If cells(testrows, 7).value < 0 then count = count +1
If cells(testrows, 11).value < 0 then count = count +1

if count 0 Then
Cells(TestRows, 3).EntireRow.Hidden = False
Else
Cells(TestRows, 3).EntireRow.Hidden = True
End If

Next TestRows

--------------------------------

Second Attempt

Dim TestRows As Integer
Dim CellValue3 As String
Dim CellValue7 As String
Dim CellValue11 As String

' Check and store amounts shown in rows 18 - 25 in columns 3,
7, and 11

For TestRows = 18 To 25
CellValue3 = Cells(TestRows, 3).Text
CellValue7 = Cells(TestRows, 7).Text
CellValue11 = Cells(TestRows, 11).Text

' Print message boxes to show values for troubleshooting
MsgBox ("row " & TestRows & "column 3 = " & CellValue3)
MsgBox ("row " & TestRows & "column 7 = " & CellValue7)
MsgBox ("row " & TestRows & "column 11 = " & CellValue11)
If CellValue3 < "0" And CellValue3 < "0%" And CellValue7
< "0"_
And CellValue7 < "0%" And CellValue11 < "0" And
CellValue3 < "0%" Then
Cells(TestRows, 3).EntireRow.Hidden = False
Else
Cells(TestRows, 3).EntireRow.Hidden = True
End If

Next TestRows

------------------------------

Neither of these attempts have worked. I'm sure my problem is
somewhere in my variable types, but I'm not sure about what's wrong or
how to make it all work, and work efficiently. I'd appreciate any
suggestions you have!

Passing formula value to variable, hide rows
 

Hi Punsterr

Sub test()
'Assuming columns to be tested are D, G and J
Application.ScreenUpdating = False
Dim r As Range, c As Range, rr As Range
Set rr = Nothing
With ActiveSheet
Set r = .Range(.Range("D1"), .Range("D" & Rows.Count).End(xlUp))
r.EntireRow.Hidden = False
For Each c In r
If c = 0 And c.Offset(0, 3) = 0 And c.Offset(0, 6) = 0 Then
If rr Is Nothing Then
Set rr = c
Else
Set rr = Union(rr, c)
End If
End If
Next c
If Not rr Is Nothing Then rr.EntireRow.Hidden = True
End With
Application.ScreenUpdating = True
End Sub


--

Regards

William

XL2003




"Punsterr" wrote in message
oups.com...
I'm creating a macro that will check each row to determine the values
of three separate columns. If any of those three values are something
other than zero, then the macro will not Hide the row. Otherwise, if
all three values are zero, then the macro will hide the row.

The catch to this one is that 1) the cells we're checking are
formula-driven, and 2) the cell values that we're testing are typically
displayed in percentage terms. I'm having difficulty getting the value
to pass through the testing subroutine. I'm not sure if I should be
trying to convert the formula value to text first, and then test it, or
if there's a better way.

Here is a sample of a couple of different ways I've tried this:

----------------------
First Attempt


Dim TestRows As Integer
Dim count As Integer

For TestRows = 18 To 25
count = 0
If cells(testrows, 3).value < 0 then count = count +1
If cells(testrows, 7).value < 0 then count = count +1
If cells(testrows, 11).value < 0 then count = count +1

if count 0 Then
Cells(TestRows, 3).EntireRow.Hidden = False
Else
Cells(TestRows, 3).EntireRow.Hidden = True
End If

Next TestRows

--------------------------------

Second Attempt

Dim TestRows As Integer
Dim CellValue3 As String
Dim CellValue7 As String
Dim CellValue11 As String

' Check and store amounts shown in rows 18 - 25 in columns 3,
7, and 11

For TestRows = 18 To 25
CellValue3 = Cells(TestRows, 3).Text
CellValue7 = Cells(TestRows, 7).Text
CellValue11 = Cells(TestRows, 11).Text

' Print message boxes to show values for troubleshooting
MsgBox ("row " & TestRows & "column 3 = " & CellValue3)
MsgBox ("row " & TestRows & "column 7 = " & CellValue7)
MsgBox ("row " & TestRows & "column 11 = " & CellValue11)
If CellValue3 < "0" And CellValue3 < "0%" And CellValue7
< "0"_
And CellValue7 < "0%" And CellValue11 < "0" And
CellValue3 < "0%" Then
Cells(TestRows, 3).EntireRow.Hidden = False
Else
Cells(TestRows, 3).EntireRow.Hidden = True
End If

Next TestRows

------------------------------

Neither of these attempts have worked. I'm sure my problem is
somewhere in my variable types, but I'm not sure about what's wrong or
how to make it all work, and work efficiently. I'd appreciate any
suggestions you have!