PDA

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



danesrood
04-01-2011, 05:36 AM
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
04-01-2011, 07:31 AM
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
04-01-2011, 07:50 AM
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
04-01-2011, 09:14 AM
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
04-01-2011, 10:17 AM
Or select your range first then
For Each cll In Selection

danesrood
04-01-2011, 01:14 PM
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
04-01-2011, 04:59 PM
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
04-01-2011, 06:09 PM
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
04-05-2011, 07:22 AM
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
04-05-2011, 03:51 PM
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
04-11-2011, 11:22 AM
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
04-11-2011, 01:27 PM
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
04-11-2011, 01:50 PM
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
04-11-2011, 02:33 PM
Brian

Thank you so much for your response.

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

BrianMH
04-11-2011, 11:09 PM
sorry change the line to

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

danesrood
04-12-2011, 02:51 AM
Brian

That works fine now thank you so much

danesrood
04-12-2011, 03:02 AM
mdmackillop & mancubus

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