Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a range using code
I have a column that has a list of site names. I need some sort of a
function that'll search through the column and hide each row that contains the value of a string variable. the function will be run when I uncheck a checkbox. And then when I recheck the checkbox I need it to unhide those rows. I've been trying to figure this out using a loop but I don't think I'm doing it correctly. According to the code that I've written it's supposed to start at the top of the column and find the first instance of a cell that matches my Site variable and then assign the row number to the StartRow variable and then continuing until it finds the last instance of the Site variable and assign the row number to the EndRow variable. And then I use the Rows(StartRow & ":" & EndRow).hidden = true function. The problem with this is that it assumes that all of my sites are going to be grouped together in a contiguous block, which will not always be the case. Here's my code: ------------------------------------------------------------------------------------------ Dim SiteRange As String Dim Counter As Int16 = 1 Dim SearchCriteria As String Dim StartRow As String Dim EndRow As String Dim MyFilterRange As String SiteRange = Sheets("Masson-Predator").Cells(Counter, 5) 'Start at the top of the column. SearchCriteria = "China Lake CLS" Do Until SiteRange = SearchCriteria 'Loop through the cells in the sites column. StartRow = Counter Counter = Counter + 1 Do Until SiteRange < SearchCriteria EndRow = Counter - 1 MyFilterRange = StartRow & ":" & EndRow Loop Loop If Me.cboChinaLakeCLS.Checked = False Then Sheets("Masson-Predator").Rows(MyFilterRange).Hidden = True Else Sheets("Masson-Predator").Rows(MyFilterRange).Hidden = False End If ----------------------------------------------------------------------------------------------- Can anyone help me out with this one? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a range using code
Hi Jonathan
maybe using auto filter to filter out the rows would work Sub HideRows() Dim wss As Worksheet Dim Test As String Application.ScreenUpdating = False Test = "China Lake CLS" Set ws = ThisWorkbook.Sheets("Masson-Predator") If ws.AutoFilterMode = False Then ws.Range("E1").AutoFilter End If Selection.AutoFilter Field:=1, Criteria1:="<" & Test Application.ScreenUpdating = True End Sub -- Regards Roger Govier "Jonathan Brown" wrote in message ... I have a column that has a list of site names. I need some sort of a function that'll search through the column and hide each row that contains the value of a string variable. the function will be run when I uncheck a checkbox. And then when I recheck the checkbox I need it to unhide those rows. I've been trying to figure this out using a loop but I don't think I'm doing it correctly. According to the code that I've written it's supposed to start at the top of the column and find the first instance of a cell that matches my Site variable and then assign the row number to the StartRow variable and then continuing until it finds the last instance of the Site variable and assign the row number to the EndRow variable. And then I use the Rows(StartRow & ":" & EndRow).hidden = true function. The problem with this is that it assumes that all of my sites are going to be grouped together in a contiguous block, which will not always be the case. Here's my code: ------------------------------------------------------------------------------------------ Dim SiteRange As String Dim Counter As Int16 = 1 Dim SearchCriteria As String Dim StartRow As String Dim EndRow As String Dim MyFilterRange As String SiteRange = Sheets("Masson-Predator").Cells(Counter, 5) 'Start at the top of the column. SearchCriteria = "China Lake CLS" Do Until SiteRange = SearchCriteria 'Loop through the cells in the sites column. StartRow = Counter Counter = Counter + 1 Do Until SiteRange < SearchCriteria EndRow = Counter - 1 MyFilterRange = StartRow & ":" & EndRow Loop Loop If Me.cboChinaLakeCLS.Checked = False Then Sheets("Masson-Predator").Rows(MyFilterRange).Hidden = True Else Sheets("Masson-Predator").Rows(MyFilterRange).Hidden = False End If ----------------------------------------------------------------------------------------------- Can anyone help me out with this one? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a range using code
This is a good suggestion Roger, I've attempted to use this method already
and found one problem with it. There may be times when I want to filter out more than one site. I actually have several checkboxes, each one representing a different site. In my example below I used China Lake CLS as one of those sites. If I use Selection.AutoFilter Field:=1, Criteria1:="<" & Test Then it'll work great for that one site, but then if I uncheck China Lake CLS and then uncheck Canon thereafter to exclude that site as well then what will happen is it'll redisplay China Lake CLS and then hide Canon. When I recheck one of the boxes, then that's a whole other ball of wax. However, I've looked into the Criteria1:Array(...) argument and found that I can include all of the sites in the array except the one that I don't want displayed. So this led me to a different idea. I thought I'd try the following code: ----------------------------------------------------------------------------------------------- Dim checkbox As CheckBox Dim ArrayString As String ArrayString = "" For Each checkbox In Me.GroupFilters.Controls If checkbox.Checked = True Then ArrayString = ArrayString & "', " & CStr(checkbox.Text) & "'," End If Next Thisworkbook.ActiveSheet.Range("$E$1").AutoFilter( Field:=1, Criteria1:="Array(" & ArrayString & "=" & ")", Operator:=Excel.XlAutoFilterOperator.xlFilterValue s) ----------------------------------------------------------------------------------------------- I run into problems though building my arraystring. How do I include quotes within quotes? ArrayString = ArrayString & "', " & CStr(checkbox.Text) & "'," The arraystring needs to look something like: "China Lake CLS", "Canon", "Site 3", "Site 4"...etc. If I can get build a proper arraystring then you're right, Roger, I can use the Autofilter method rather than build some function to find the rows individually and hide them. What'dya think? "Roger Govier" wrote: Hi Jonathan maybe using auto filter to filter out the rows would work Sub HideRows() Dim wss As Worksheet Dim Test As String Application.ScreenUpdating = False Test = "China Lake CLS" Set ws = ThisWorkbook.Sheets("Masson-Predator") If ws.AutoFilterMode = False Then ws.Range("E1").AutoFilter End If Selection.AutoFilter Field:=1, Criteria1:="<" & Test Application.ScreenUpdating = True End Sub -- Regards Roger Govier "Jonathan Brown" wrote in message ... I have a column that has a list of site names. I need some sort of a function that'll search through the column and hide each row that contains the value of a string variable. the function will be run when I uncheck a checkbox. And then when I recheck the checkbox I need it to unhide those rows. I've been trying to figure this out using a loop but I don't think I'm doing it correctly. According to the code that I've written it's supposed to start at the top of the column and find the first instance of a cell that matches my Site variable and then assign the row number to the StartRow variable and then continuing until it finds the last instance of the Site variable and assign the row number to the EndRow variable. And then I use the Rows(StartRow & ":" & EndRow).hidden = true function. The problem with this is that it assumes that all of my sites are going to be grouped together in a contiguous block, which will not always be the case. Here's my code: ------------------------------------------------------------------------------------------ Dim SiteRange As String Dim Counter As Int16 = 1 Dim SearchCriteria As String Dim StartRow As String Dim EndRow As String Dim MyFilterRange As String SiteRange = Sheets("Masson-Predator").Cells(Counter, 5) 'Start at the top of the column. SearchCriteria = "China Lake CLS" Do Until SiteRange = SearchCriteria 'Loop through the cells in the sites column. StartRow = Counter Counter = Counter + 1 Do Until SiteRange < SearchCriteria EndRow = Counter - 1 MyFilterRange = StartRow & ":" & EndRow Loop Loop If Me.cboChinaLakeCLS.Checked = False Then Sheets("Masson-Predator").Rows(MyFilterRange).Hidden = True Else Sheets("Masson-Predator").Rows(MyFilterRange).Hidden = False End If ----------------------------------------------------------------------------------------------- Can anyone help me out with this one? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a range using code
Hi Jonathan
You need to use 3 double quotes in VBA to get a single double quote i.e. for each double quote you add to a string, it must be enclosed inside a set of double quotes """ ArrayString = ArrayString & """ & CStr(checkbox.Text) & ""," Miss out that first "," you had, otherwise you will have too many delimiters. When Array string is null, you add the text plus a comma, to put the delimiter at the end of the first string. At the lend of your loop, you will need to trim off the final comma Array String = LEFT(ArrayString,LEN(Array String)-1) -- Regards Roger Govier "Jonathan Brown" wrote in message ... This is a good suggestion Roger, I've attempted to use this method already and found one problem with it. There may be times when I want to filter out more than one site. I actually have several checkboxes, each one representing a different site. In my example below I used China Lake CLS as one of those sites. If I use Selection.AutoFilter Field:=1, Criteria1:="<" & Test Then it'll work great for that one site, but then if I uncheck China Lake CLS and then uncheck Canon thereafter to exclude that site as well then what will happen is it'll redisplay China Lake CLS and then hide Canon. When I recheck one of the boxes, then that's a whole other ball of wax. However, I've looked into the Criteria1:Array(...) argument and found that I can include all of the sites in the array except the one that I don't want displayed. So this led me to a different idea. I thought I'd try the following code: ----------------------------------------------------------------------------------------------- Dim checkbox As CheckBox Dim ArrayString As String ArrayString = "" For Each checkbox In Me.GroupFilters.Controls If checkbox.Checked = True Then ArrayString = ArrayString & "', " & CStr(checkbox.Text) & "'," End If Next Thisworkbook.ActiveSheet.Range("$E$1").AutoFilter( Field:=1, Criteria1:="Array(" & ArrayString & "=" & ")", Operator:=Excel.XlAutoFilterOperator.xlFilterValue s) ----------------------------------------------------------------------------------------------- I run into problems though building my arraystring. How do I include quotes within quotes? ArrayString = ArrayString & "', " & CStr(checkbox.Text) & "'," The arraystring needs to look something like: "China Lake CLS", "Canon", "Site 3", "Site 4"...etc. If I can get build a proper arraystring then you're right, Roger, I can use the Autofilter method rather than build some function to find the rows individually and hide them. What'dya think? "Roger Govier" wrote: Hi Jonathan maybe using auto filter to filter out the rows would work Sub HideRows() Dim wss As Worksheet Dim Test As String Application.ScreenUpdating = False Test = "China Lake CLS" Set ws = ThisWorkbook.Sheets("Masson-Predator") If ws.AutoFilterMode = False Then ws.Range("E1").AutoFilter End If Selection.AutoFilter Field:=1, Criteria1:="<" & Test Application.ScreenUpdating = True End Sub -- Regards Roger Govier "Jonathan Brown" wrote in message ... I have a column that has a list of site names. I need some sort of a function that'll search through the column and hide each row that contains the value of a string variable. the function will be run when I uncheck a checkbox. And then when I recheck the checkbox I need it to unhide those rows. I've been trying to figure this out using a loop but I don't think I'm doing it correctly. According to the code that I've written it's supposed to start at the top of the column and find the first instance of a cell that matches my Site variable and then assign the row number to the StartRow variable and then continuing until it finds the last instance of the Site variable and assign the row number to the EndRow variable. And then I use the Rows(StartRow & ":" & EndRow).hidden = true function. The problem with this is that it assumes that all of my sites are going to be grouped together in a contiguous block, which will not always be the case. Here's my code: ------------------------------------------------------------------------------------------ Dim SiteRange As String Dim Counter As Int16 = 1 Dim SearchCriteria As String Dim StartRow As String Dim EndRow As String Dim MyFilterRange As String SiteRange = Sheets("Masson-Predator").Cells(Counter, 5) 'Start at the top of the column. SearchCriteria = "China Lake CLS" Do Until SiteRange = SearchCriteria 'Loop through the cells in the sites column. StartRow = Counter Counter = Counter + 1 Do Until SiteRange < SearchCriteria EndRow = Counter - 1 MyFilterRange = StartRow & ":" & EndRow Loop Loop If Me.cboChinaLakeCLS.Checked = False Then Sheets("Masson-Predator").Rows(MyFilterRange).Hidden = True Else Sheets("Masson-Predator").Rows(MyFilterRange).Hidden = False End If ----------------------------------------------------------------------------------------------- Can anyone help me out with this one? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code - Find & Move | Excel Discussion (Misc queries) | |||
Code to find code | Excel Discussion (Misc queries) | |||
Find last row code | Excel Discussion (Misc queries) | |||
Why code for find() is not working | Excel Discussion (Misc queries) | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) |