Hide Row If

[GregR]

I would like to hide all rows in Column J that do not contain text
"*899*" (part of account number, formatted as 000-899-000). The code
below doesn't do it. TIA

Sub HideProjectRows()
Dim intCounter As Integer
Dim Str As String

Str = "*899*"

With Sheets("Projects 05")
For intCounter = 2 To 487
If .Cells(intCounter, "J").Value <> Str Then _
.Rows(intCounter).Hidden = True
Next intCounter
End With
End Sub

Greg

 

[Jim Thomlinson]

Sub HideProjectRows()
Dim intCounter As Integer
Dim Str As String

Str = "-899-"

With Sheets("Projects 05")
For intCounter = 2 To 487
If instr(.Cells(intCounter, "J").text , Str)>0 Then _
.Rows(intCounter).Hidden = True
Next intCounter
End With
End Sub

 

[Eric]

Try this code

the wild card concept does not work because when using a srting * is also
considered a character. You have different approaches but since yours seems
static enough (middle part) its safe to use Mid to remove the portion you
need to validate from the string variable you submit.

Sub HideProjectRows()
Dim intCounter As Long
Dim Str As String
Dim rng As Range
Dim valueCheck As String

Str = "899"

Set rng = Sheets("Projects 05").UsedRange.Rows ' Set sheet for all range
rows populated

With Sheets("Projects 05")
For intCounter = 2 To rng.Rows.Count ' to maximum rows populated
valueCheck = .Cells(intCounter, "J").Value 'Isolate string value
from row
valueCheck = Mid(valueCheck, 5, 3) 'Isolate validation portion
If valueCheck <> Str Then
.Rows(intCounter).Hidden = True
End If
Next intCounter
End With

End Sub

Hope it helps

 

[Rick Hansen]

Morning Greg,
I made a few changes to you code, You was try find a complete string
with your code. The code below will find the sub string. If it's not found,
it returns zero.

enjoy, Rick

PS: make sure to put in the "End if" statement after
..Rows(intCounter).Hidden = True
It was missing in your code...


Sub HideProjectRows()

Dim intCounter As Integer
Dim Str As String '

Str = "899"

With Sheets("Projects 05")
For intCounter = 2 To 487
If InStr(.Cells(intCounter, "J"), Str) = 0 Then
.Rows(intCounter).Hidden = True
End If
Next intCounter
End With
End Sub

 

[Jim Thomlinson]

Just to clarify, Greg's original code has an underscore at the end of the if
criteria as a line connector so the code did not need an end if. That being
said i think you might be right on the "= 0" now that I re-read the request.
My code was hiding all of the wrong stuff...

 

[GregR]

Jim, Eric, Rick, you solved my dilemma, thank you

Greg