Solved: Hide/Show Rows based on % character in cell

[danesrood]

Dear All

I have a need to regularly select a range of cells, say for example A3:Q56 where I need to hide the row if within the string of the cell in column A there is the string " % " i.e a % with a space on either side.

Conversely I would also need to be able to hide all rows that don't contain the " % " string.

I hope that this makes sense

[mancubus]

hi.
test below code with a backup file.


Sub HideRowsOnCond()
Dim cll as Range
For Each cll in Range("A3:Q56")
If Instr(1, cll.Value, " % ") > 0 then
cll.Entirerow.Hidden = True
End if
Next
End sub

[danesrood]

mancubus

Thank you for your response.

In running your code I get a Run time error 13 error type mismatch .

The range I mentioned was just for descriptive purposes as I would really want to select my own range each time rather than having to adjust the code each time.

[mancubus]

for getting a range:
http://www.jkp-ads.com/articles/SelectARange.asp



Sub HideRowsOnCond()

Dim cll As Range, SrcRng As Range

Set SrcRng = Application.InputBox(Prompt:="Pls select range", Title:="RANGE", Type:=8)

For Each cll In SrcRng
If InStr(1, cll.Value, " % ") > 0 Then
cll.EntireRow.Hidden = True
End If
Next
End Sub


worked for me.
pls see the attached file.

[mdmackillop]

Or select your range first then
For Each cll In Selection

[danesrood]

mancubus

Thats fine thank you so much.

Would it be possible to produce a version that preferably unhides any rows within the selected range and then hides the rows that don't have the " % " string.

Again my sincere thanks and also a thank you to mdmackillop for your suggestion.

[mancubus]

sorry for the second part.

coding "cells that do not contain" case is a bit tricky. if any cell in that row does not contain the text, row will be hidden. even if another cell contains...

below code looks for the values in column A. so if cells in other columns may also contain " % " text, the code must be modified accordingly.



Sub HideUnhideRowsOnCond()

Dim LR As Long, lRow As Long
Dim Response

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

LR = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

ActiveSheet.UsedRange.Rows.Hidden = False

Response = MsgBox("Do you want to hide rows based on" & vbCrLf & _
"- cells that contain search text? Press Yes!" & vbCrLf & _
"- cells that do not contain search text? Press No!", _
vbYesNoCancel, "Hide Criteria")

Select Case Response
Case vbYes
For lRow = LR To 2 Step -1
If InStr(1, Range("A" & lRow).Value, " % ") > 0 Then
Rows(lRow).Hidden = True
End If
Next
Case vbNo
For lRow = LR To 2 Step -1
If InStr(1, Range("A" & lRow).Value, " % ") = 0 Then
Rows(lRow).Hidden = True
End If
Next
Case vbCancel
Exit Sub
End Select


With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub





see the kb:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=416
http://www.vbaexpress.com/kb/getarticle.php?kb_id=512

[mdmackillop]

Sub HidePC()

Dim rng As Range

Set rng = Selection.Offset(-1).Resize(Selection.Count + 1)

Select Case MsgBox("Hide %", vbYesNoCancel)
Case vbYes
rng.AutoFilter field:=1, Criteria1:="<>* % *", visibledropdown:=False
Case vbNo
rng.AutoFilter field:=1, Criteria1:="=* % *", visibledropdown:=False
Case vbCancel
If ActiveSheet.FilterMode Then rng.AutoFilter
End Select
End Sub

[danesrood]

Hi mancubus & mdmackillop

Sorry for the delay in getting back and I would like to thank you both for the time spent on trying to resolve this issue.

My regards from a gloomy England

[mdmackillop]

Better is to know if the issue if solved. If not, what is the problem?

PS: If England if gloomy, you could in England is gloomy could always move North to the land of the palm trees
.

[danesrood]

mdmackillop

Sorry to have been a bit vague with my final response but the suggestions didn't appear to be working for lower blocks of data.

I have attached a version of the sheet which hopefully makes sense

What I would prefer to do is to select a group of rows say A32:A54 and hide all of the rows that contain a % character.

If you are able to help it would be great.

Regards from a lovely sunny and warm Southern England.

[BrianMH]

Sub HidePC()

Dim rng As Range
Dim arows
Dim rngFound As Range
Dim rngCells As Range
Dim strStart As String
ReDim arows(0)
Set rng = Selection
rng.Rows.Hidden = False
Set rngFound = rng.("%", , xlValues)
If rngFound Is Nothing Then
MsgBox ("no % signs")
Exit Sub
Else
strStart = rngFound.Address
Set rngCells = rngFound
End If
Do
Set rngFound = rng.FindNext(rngFound)
Set rngCells = Application.Union(rngCells, rngFound)
Loop While Not strStart = rngFound.Address
rngCells.Rows.Hidden = True




End Sub

This should work for you.

[Aussiebear]

If England if gloomy, you could in England is gloomy could always move North to the land of the palm trees
.

Take an extra tank of fuel because you won't make it there on one!!! :devil2:

[danesrood]

Brian

Thank you so much for your response.

However, it has broken down at Set rngFound = rng.("%", , xlValues) as a
compile error.

[BrianMH]

sorry change the line to

Set rngFound = rng.Find("%", , xlValues)

[danesrood]

Brian

That works fine now thank you so much

[danesrood]

mdmackillop & mancubus

Thank you for taking the time to have a look at my problem which is much appreciated.