Hi,
On one sheet called "Control", I have two columns, one is "Area Size" and one is "Show?". Like this:
Area size Show?
0-19,999 Yes
20,000-39,999 Yes
40,000-59,999 Yes
60,000-79,999 Yes
80,000-99,999 Yes
100,000-119,999 Yes
120,000-139,999 Yes
140,000-159,999 Yes
160,000-179,999 Yes
180,000-199,999 Yes
200,000-219,999 Yes
220,000+ Yes
Depending on the text in column F (Show column), I want to autofilter data in a sheet called "Output". In output, I have 200 rows of data and a column D that contains the area grouping of each building. This is my current VBA code, however it is not working, and whenever I select Yes or No in the control sheet, it filters out all of the data as if none match the criteria, when I know it does.
Sub ShowBuildings()
Dim wsC As Worksheet, wsO As Worksheet
Set wsC = Worksheets("Control")
Set wsO = Worksheets("Output")
Dim x As Long, sList() As String
x = 0
With wsC
Dim rng As Range
For Each rng In .Range("F2:F13")
If rng.Value2 = "Yes" Then
ReDim Preserve sList(x)
sList(x) = rng.Offset(, -1)
x = x + 1
End If
Next
End With
With wsO
.AutoFilterMode = False
With .Range(.Range("D2"), .Range("D" & .Rows.Count).End(xlUp))
If x > 0 Then 'checks to see if array is empty because no "yes'" were selected
.AutoFilter Field:=1, Criteria1:=sList, Operator:=xlFilterValues
Else
.AutoFilter
End If
End With
End With
End Sub
Any idea as to why this isn't working?
Bookmarks