Will someone please explain this code - only a few lines?

[msb1977]

Hi all

I'm a novice with VBA and am trying to understand what this section of code means and what it actually does. I know the end result is that it is used to set the range for filtering from a list.

Dim wbBudget              As Workbook 
Dim wbOSR                  As Workbook  
Dim Criteria_Proj(100)   As String    
Dim FirstRow                As Integer    

FirstRow = 1
While wbBudget.Sheets("Filter Lookup").Cells(FirstRow + 1, 2) <> ""
   Criteria_Proj(FirstRow) = wbBudget.Sheets("Filter Lookup").Cells(FirstRow + 1, 2)
   FirstRow = FirstRow + 1
Wend

This part I don't need explained, but am including for reference.

wbOSR.Sheets(1).Range("Table1").AutoFilter Field:=6, Criteria1:=Criteria_Proj, Operator:=xlFilterValues

 

[marcelocbd]

You could be a little more clear in what you are asking, but let's give it a try.

The While structure is looping down in the "B" column starting in the second row until it finds a blank cell, when it stops.
While looping it adds all cell values to an array with up to 100 elements in it, so it can later filter the sixth column in table1 with multiple criteria (all elements value s in the array)

Hope i've helped.

 

[msb1977]

Thanks. That makes perfect sense. If you don't mind I have a few more questions...

1. Can Criteria_Proj(100) be adjusted to say 25 or 1000 or is 100 standard syntax?

2. If it loops until it finds a blank cell, why does it still filter on blanks?

 

[marcelocbd]

Thanks. That makes perfect sense. If you don't mind I have a few more questions...

1. Can Criteria_Proj(100) be adjusted to say 25 or 1000 or is 100 standard syntax?

2. If it loops until it finds a blank cell, why does it still filter on blanks?

1. You can adjust the 100 to whatever number you want. Actualy I don't know if there is a maximum value, but if there is one I belive it's gonna be really big
2. What do you mean by filter on blank cells? If you post the file it really helps

 

[msb1977]

1. You can adjust the 100 to whatever number you want. Actualy I don't know if there is a maximum value, but if there is one I belive it's gonna be really big
2. What do you mean by filter on blank cells? If you post the file it really helps

Sorry, but I can't post the actual file. Below is the column that relates to Criteria_Proj. When the code filters, it filters everything perfectly except it also includes blank cells. I would like to exclude blank cells.

2P_SO08D_FY16
2P_SO08P_FY16
2P_SO08S_FY16
S3_ST08D_FY16
S3_ST08P_FY16
X3_SC08D_FY16
X3_SC08L_FY16
X3_SC08P_FY16
X3_SC08S_FY16
X3_SD08D_FY16
X3_SD08L_FY16
X3_SD08P_FY16
X3_SD08S_FY16
Y3_SD97D_FY16
Y3_SD97L_FY16
Y3_SD97P_FY16
Y3_SD97S_FY16

Here is the code again. I appreciate your help!

Dim wbBudget              As Workbook 
Dim wbOSR                  As Workbook  
Dim Criteria_Proj(100)   As String    
Dim FirstRow                As Integer    

FirstRow = 1
While wbBudget.Sheets("Filter Lookup").Cells(FirstRow + 1, 2) <> ""
   Criteria_Proj(FirstRow) = wbBudget.Sheets("Filter Lookup").Cells(FirstRow + 1, 2)
   FirstRow = FirstRow + 1
Wend

wbOSR.Sheets(1).Range("Table1").AutoFilter Field:=6, Criteria1:=Criteria_Proj, Operator:=xlFilterValues

 

[skywriter]

Proj(100) is creating an array with the potential to put 101 elements in it.

This was probably done to make sure you had enough room in the array to accommodate all of your possible data.

If the data you have falls short of this number you are left with a bunch of blank elements, therefore when you use autofilter not only are you asking it to filter by the elements that have values you are asking autofilter to filter by the blanks also.

 

[msb1977]

Proj(100) is creating an array with the potential to put 101 elements in it.

This was probably done to make sure you had enough room in the array to accommodate all of your possible data.

If the data you have falls short of this number you are left with a bunch of blank elements, therefore when you use autofilter not only are you asking it to filter by the elements that have values you are asking autofilter to filter by the blanks also.

That makes perfect sense

Is it possible to make the array variable based on the last row:

Dim Criteria_Proj(LastRow) As String

and then define the last row of the column?

 

[skywriter]

I'm working on it, give me a few minutes.

 

[marcelocbd]

That makes perfect sense

Is it possible to make the array variable based on the last row:

Dim Criteria_Proj(LastRow) As String

and then define the last row of the column?

Never tried that, but it is worth a shot

 

[skywriter]

This assumes you do NOT has an Option Base 1 statement at the top of your module, if you do let me know I'll edit the code.

Sub Test()
Dim wbBudget              As Workbook
Dim wbOSR                  As Workbook
Dim Criteria_Proj()   As String
Dim rngData As Range, r As Range, c As Long

    With wbBudget.Worksheets("Filter Lookup")
        Set rngData = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
    End With
    
    ReDim Criteria_Proj(rngData.Cells.Count - 1)
    c = 0
    
    For Each r In rngData
        Criteria_Proj(c) = r.Value
        c = c + 1
    Next r