hiding rows

[Guest]

I've got a table that goes form rows 24 to 158 and from colums D to DC and I
want the macro to search colums S, AB, AK, AT, BC, BL, BU, CD, CM, CV, and if
any cell in one of these columns has a number equal to or lower that -0.012
then that row I want shown, the rest hidden. How would I go about
constructing a macro to do this?

 

[Guest]

Here is some code. Blank columns will be made invisible.

Sub test()

Dim SearchCol As Variant
Dim difference As Double

SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV")

For Each col In SearchCol
found = False
For RowCount = 24 To 158
If Cells(RowCount, col).Value <= -0.012 Then

found = True
Exit For
End If

Next RowCount
If found = False Then
Columns(col).Hidden = True
End If
Next col

End Sub

 

[Guest]

I was interested in hiding the rows, those rows that contain a number 0<-0.012

 

[Guest]

Easily fixed. Less than in math means from negative infinity to -0.012. You
want
-0.012 < x <= 0.

Sub test()

Dim SearchCol As Variant
Dim difference As Double

SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV")

For Each col In SearchCol
found = False
For RowCount = 24 To 158
If (Cells(RowCount, col).Value > -0.012) and _
(Cells(RowCount, col).Value <= 0) Then

found = True
Exit For
End If

Next RowCount
If found = False Then
Columns(col).Hidden = True
End If
Next col

End Sub

 

[Guest]

Sorry, ignore my last question, when I read your answer I realized I didn't
really want to ask that. What I meant was that the rows in question are not
hidden with the procedure you gave me, the firt one.

 

[Guest]

I'm confused by the wording of your question below. I think you want to
reverse which columns are hidden and which are not hidden. You have to just
change True to False

This code hides values that were not FOUND
If found = False Then
Columns(col).Hidden = True
End If
This code hides values that were FOUND
If found = True Then
Columns(col).Hidden = True
End If

 

[Guest]

You see, from what I understand from your code ( i know just the basics)
Columns(col).Hidden = True
means we are talking about hiding columns, and I am interested in hiding rows

 

[Guest]

you see, I am looking for something like this but I want more than column 19
checked for the condition, I want a number of columns checked. Besides, this
procedure is quite slow but it works, at least for one column

BeginRow = 24
EndRow = 158
ChkCol = 19

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value <= -0.012 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt

 

[Guest]

I just reverse the rows and columns. Looking at your code it is better to
reference columns using letters
instead of this
cells(5,19)
use this
cells(5,"S")
Who remembers that the letter S is the 19th letter of the alphabet.
Or like you did it in your code
chkCol = "S"
cells(5,chkCol)

Sub test()

Dim SearchCol As Variant
Dim difference As Double

SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV")

For RowCount = 24 To 158
found = False
For Each col In SearchCol
If Cells(RowCount, col).Value <= -0.012 Then

found = True
Exit For
End If

Next col
If found = False Then
Rows(RowCount).Hidden = True
End If
Next RowCount

End Sub

 

[Guest]

Dim Cell As Range
Dim TestRow As Range
Dim CellsInRow As String

Rows.Hidden = False

For Each TestRow In Range("A24:A158")
CellsInRow = Replace("S#,AB#,AK#,AT#,BC#,BL#,BU#,CD#,CM#,CV#", "#",
TestRow.Row)

For Each Cell In Range(CellsInRow)
If Cell.Value <= -0.012 Then
Rows(Cell.Row).Hidden = True
Exit For
End If
Next Cell

Next TestRow

 

[Guest]

Sorry for taking so long to get back to you. I have just tried the macro and
it works. Thank you.

 

[Guest]

Thank you but the macro doesn't work. It doesn't seem to like the part:
CellsInRow = Replace("S#,AB#,AK#,AT#,BC#,BL#,BU#,CD#,CM#,CV#", "#",