Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to Hide columns in spreadsheet but NOT hide data in chart. | Charts and Charting in Excel | |||
Hide/Unhide columns using button on top over relevant columns | Excel Discussion (Misc queries) | |||
I set up a macro to hide/unhide columns. It hides more columns | Excel Programming | |||
VBA to hide columns | Excel Programming | |||
Excel button :: Filter columns by value - possible? Additionally, hide certain columns | Excel Programming |