Find a range using code

[Jonathan Brown]

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

 

[Roger Govier]

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

 

[Jonathan Brown]

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.xlFilterValues)
-----------------------------------------------------------------------------------------------

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?

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

 

[Roger Govier]

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

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.xlFilterValues)
-----------------------------------------------------------------------------------------------

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?