Hide Row Per Value in Two Columns
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 ------------------------------ Thanks in advance for your help, Derrick |
Hide Row Per Value in Two Columns
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. " wrote: 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 ------------------------------ Thanks in advance for your help, Derrick |
Hide Row Per Value in Two Columns
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 " wrote: 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 ------------------------------ Thanks in advance for your help, Derrick |
Hide Row Per Value in Two Columns
On Feb 23, 3:38 pm, Tom Ogilvy
wrote: 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 " wrote: 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 ------------------------------ Thanks in advance for your help, Derrick- Hide quoted text - - Show quoted text - You guys are great. Thanks for the help. |
All times are GMT +1. The time now is 01:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com