Hide Row Per Value in Two Columns

D

derrick.perkins

I know this has been posted a lot, and believe me I searched
everything, but for some reason I cannot get this to work.

I have a spreadsheet with 250 rows and lots of columns. I would like
to hide the rows that have the value "HideMe" in column A or B.
However, when using the Visual Basic I found online, I cannot get this
to work.

When I use one script for one column and then another script for the
next column, it unhides what was previously hidden.

Here is the script that I am using:
------
Sub HideRows()
Sheets("IRR").Select
Call HideA
Call HideB
End Sub

Sub HideA()
Dim sToFind As String
Dim clastrow As Long
Dim myRange As Range


With ActiveSheet
clastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A1").EntireRow.Insert
sToFind = "HideMe"
.Columns("A:A").AutoFilter Field:=1, Criteria1:=sToFind
Set myRange = .Rows("2:" & clastrow + _
1).SpecialCells(xlCellTypeVisible).Rows
.Range("A1").EntireRow.Delete
End With
myRange.Hidden = True

End Sub

Sub HideB()
Dim sToFind As String
Dim clastrow As Long
Dim myRange As Range


With ActiveSheet
clastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A1").EntireRow.Insert
sToFind = "HideMe"
.Columns("B:B").AutoFilter Field:=1, Criteria1:=sToFind
Set myRange = .Rows("2:" & clastrow + _
1).SpecialCells(xlCellTypeVisible).Rows
.Range("A1").EntireRow.Delete
End With
myRange.Hidden = True

End Sub
 
J

Joel

It seems to work the 1st time. to get it to work again I go back to te excel
worksheet and go to the Data Menu - Filter - Show all. It is filtering the
1st 17 rowes of the worksheet.
 
T

Tom Ogilvy

Sub ABC()
Dim lastrow as Long
Dim i as Long
Activesheet.Usedrange.rows.Hidden = False
lastrow = cells(rows.count,1).end(xlup).row
for i = lastrow to 2 step -1
if instr(1,cells(i,1),"hideme",vbTextcompare) + _
instr(1,cells(i,2),"hideme",vbTextcompare) then
rows(i).Hidden = True
end if
Next
End sub
 
D

derrick.perkins

Sub ABC()
Dim lastrow as Long
Dim i as Long
Activesheet.Usedrange.rows.Hidden = False
lastrow = cells(rows.count,1).end(xlup).row
for i = lastrow to 2 step -1
if instr(1,cells(i,1),"hideme",vbTextcompare) + _
instr(1,cells(i,2),"hideme",vbTextcompare) then
rows(i).Hidden = True
end if
Next
End sub

--
Regards,
Tom Ogilvy














- Show quoted text -

You guys are great. Thanks for the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top