Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constrain to visible area?
I have a macro that searchs for a text string and hides any row not
containing the string (code at the end). (I got this from a post on a NG, and tried a Google search so I could give big credits to the original author, but I couldn't find the original post.) It works great, with one small hitch. If I filter my worksheet to show a range selected from the middle of the sheet, because it starts from 2, it will search even what is not visible. Would it be difficult to add some lines to constrain the search area to only what is visible after filtering? It also does this if I try to run it twice. Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If Next i End Sub Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constrain to visible area?
Sub SelectiveRowHide()
Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If end if Next i End Sub would be one interpretation of what you said. If you mean only search the visible cells in the filtered range Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer Dim rng as range myTarget = Application.InputBox("What text are you searching for?") If activesheet.Autofiltermode then set rng = Activesheet.Autofilter.Range Else set rng = Range(cells(2,1),cells(rows.count,1).End(xlup)) End if For each cell in rng.specialcells(xlvisible) i = cell.row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If End if Next cell End Sub Regards, Tom Ogilvy "Ed" wrote in message ... I have a macro that searchs for a text string and hides any row not containing the string (code at the end). (I got this from a post on a NG, and tried a Google search so I could give big credits to the original author, but I couldn't find the original post.) It works great, with one small hitch. If I filter my worksheet to show a range selected from the middle of the sheet, because it starts from 2, it will search even what is not visible. Would it be difficult to add some lines to constrain the search area to only what is visible after filtering? It also does this if I try to run it twice. Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If Next i End Sub Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constrain to visible area?
The AutoFilter only works down one column. The macro searches every cell
across the entire row; only if the string is not found in *any* cell is the row hidden. I would like to use the macro in conjunction with the AutoFilter, but need to stay within the visible range after filtering to find my "hidden string", rather than searching from Row 2. The visible range, then, could change depending on what I have filtered for. Ed "Don Guillett" wrote in message ... Wouldn't autofilter be easier? dataautofilter record a macro to see what is going on and then adapt with your input box for the criteria. -- Don Guillett SalesAid Software Granite Shoals, TX "Ed" wrote in message ... I have a macro that searchs for a text string and hides any row not containing the string (code at the end). (I got this from a post on a NG, and tried a Google search so I could give big credits to the original author, but I couldn't find the original post.) It works great, with one small hitch. If I filter my worksheet to show a range selected from the middle of the sheet, because it starts from 2, it will search even what is not visible. Would it be difficult to add some lines to constrain the search area to only what is visible after filtering? It also does this if I try to run it twice. Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If Next i End Sub Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constrain to visible area?
Tom:
I used your second example, and it works perfectly! I can filter to a general catagory, then search for my string only within the filtered range. Many thanks! For information, I'm assuming the AutoFilter does not "hide" rows? And so your first example would not work on a filtered range because it would not detect any hidden rows? Is this correct? Ed "Tom Ogilvy" wrote in message ... Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If end if Next i End Sub would be one interpretation of what you said. If you mean only search the visible cells in the filtered range Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer Dim rng as range myTarget = Application.InputBox("What text are you searching for?") If activesheet.Autofiltermode then set rng = Activesheet.Autofilter.Range Else set rng = Range(cells(2,1),cells(rows.count,1).End(xlup)) End if For each cell in rng.specialcells(xlvisible) i = cell.row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If End if Next cell End Sub Regards, Tom Ogilvy "Ed" wrote in message ... I have a macro that searchs for a text string and hides any row not containing the string (code at the end). (I got this from a post on a NG, and tried a Google search so I could give big credits to the original author, but I couldn't find the original post.) It works great, with one small hitch. If I filter my worksheet to show a range selected from the middle of the sheet, because it starts from 2, it will search even what is not visible. Would it be difficult to add some lines to constrain the search area to only what is visible after filtering? It also does this if I try to run it twice. Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If Next i End Sub Ed |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constrain to visible area?
Rows hidden by the autofilter are hidden. The only difference between the
two is that the second works only on the filtered range while the first starts with row 2. You gave the impression that you have data in areas outside the filtered data, so the first would address those as well. If the whole page is filtered, then the first macro should work as well. The second probably hides your header row, so you might want to adjust it to: Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer Dim rng as range myTarget = Application.InputBox("What text are you searching for?") If activesheet.Autofiltermode then set rng = Activesheet.Autofilter.Range Else ' changed line set rng = Range(cells(1,1),cells(rows.count,1).End(xlup)) End if 'Added line set rng = rng.offset(1,0).Resize(rng.rows.count-1) For each cell in rng.specialcells(xlvisible) i = cell.row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If End if Next cell End Sub -- Regards, Tom Ogilvy "Ed" wrote in message ... Tom: I used your second example, and it works perfectly! I can filter to a general catagory, then search for my string only within the filtered range. Many thanks! For information, I'm assuming the AutoFilter does not "hide" rows? And so your first example would not work on a filtered range because it would not detect any hidden rows? Is this correct? Ed "Tom Ogilvy" wrote in message ... Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If end if Next i End Sub would be one interpretation of what you said. If you mean only search the visible cells in the filtered range Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer Dim rng as range myTarget = Application.InputBox("What text are you searching for?") If activesheet.Autofiltermode then set rng = Activesheet.Autofilter.Range Else set rng = Range(cells(2,1),cells(rows.count,1).End(xlup)) End if For each cell in rng.specialcells(xlvisible) i = cell.row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If End if Next cell End Sub Regards, Tom Ogilvy "Ed" wrote in message ... I have a macro that searchs for a text string and hides any row not containing the string (code at the end). (I got this from a post on a NG, and tried a Google search so I could give big credits to the original author, but I couldn't find the original post.) It works great, with one small hitch. If I filter my worksheet to show a range selected from the middle of the sheet, because it starts from 2, it will search even what is not visible. Would it be difficult to add some lines to constrain the search area to only what is visible after filtering? It also does this if I try to run it twice. Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If Next i End Sub Ed |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constrain to visible area?
**IGNORE THIS ONE** I accidently ran the previous version! Yours works
fine on only the selected visible ranges; it ignored all the others. Again, many thanks. Ed "Ed" wrote in message ... Okay - just ran the second one with the sheet filterd slightly different - the filter returned two chunks out of the middle. The search started at the top of the first visible range, then continued searching through the rows that were "hidden" by the filter, then down through the last visible range. (Say Col A has rows 1-5 "A", 6-10 "B", etc. through "E". If I filtered for "B" and "D", it would have searched B, C, and D, rather than only the visible B and D ranges.) Is this just a limitation of this beast? Ed |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constrain to visible area?
Thanks, Tom. I appreciate the help. It did hide the headers, so I was
trying to figure where to put something like "If (i) = 1 Then Next (i)", but it wouldn't work. I thought the original "Range(cells(2,1)," would start the range at the second row, though, and couldn't understand why it included the first row. (Maybe it's time for something a bit more in-depth than "VBA for Dummies"?) Ed "Tom Ogilvy" wrote in message ... Rows hidden by the autofilter are hidden. The only difference between the two is that the second works only on the filtered range while the first starts with row 2. You gave the impression that you have data in areas outside the filtered data, so the first would address those as well. If the whole page is filtered, then the first macro should work as well. The second probably hides your header row, so you might want to adjust it to: Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer Dim rng as range myTarget = Application.InputBox("What text are you searching for?") If activesheet.Autofiltermode then set rng = Activesheet.Autofilter.Range Else ' changed line set rng = Range(cells(1,1),cells(rows.count,1).End(xlup)) End if 'Added line set rng = rng.offset(1,0).Resize(rng.rows.count-1) For each cell in rng.specialcells(xlvisible) i = cell.row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If End if Next cell End Sub -- Regards, Tom Ogilvy "Ed" wrote in message ... Tom: I used your second example, and it works perfectly! I can filter to a general catagory, then search for my string only within the filtered range. Many thanks! For information, I'm assuming the AutoFilter does not "hide" rows? And so your first example would not work on a filtered range because it would not detect any hidden rows? Is this correct? Ed "Tom Ogilvy" wrote in message ... Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If end if Next i End Sub would be one interpretation of what you said. If you mean only search the visible cells in the filtered range Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer Dim rng as range myTarget = Application.InputBox("What text are you searching for?") If activesheet.Autofiltermode then set rng = Activesheet.Autofilter.Range Else set rng = Range(cells(2,1),cells(rows.count,1).End(xlup)) End if For each cell in rng.specialcells(xlvisible) i = cell.row if not rows(i).Hidden then Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If End if Next cell End Sub Regards, Tom Ogilvy "Ed" wrote in message ... I have a macro that searchs for a text string and hides any row not containing the string (code at the end). (I got this from a post on a NG, and tried a Google search so I could give big credits to the original author, but I couldn't find the original post.) It works great, with one small hitch. If I filter my worksheet to show a range selected from the middle of the sheet, because it starts from 2, it will search even what is not visible. Would it be difficult to add some lines to constrain the search area to only what is visible after filtering? It also does this if I try to run it twice. Sub SelectiveRowHide() Dim myTarget As String Dim myFind As Range Dim i As Integer myTarget = Application.InputBox("What text are you searching for?") For i = 2 To Range("A65536").End(xlUp).Row Rows(i).Select Set myFind = Rows(i).Find(What:=myTarget) If myFind Is Nothing Then Selection.EntireRow.Hidden = True End If Next i End Sub Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart Area visible but cannot access rows and columns on worksheet | Charts and Charting in Excel | |||
Excel document out of visible area | Excel Discussion (Misc queries) | |||
Constrain data format in cell | Excel Discussion (Misc queries) | |||
how to constrain the sum of a range in solver | Excel Discussion (Misc queries) | |||
column truncated due to width constrain | Excel Worksheet Functions |