Hello! I have created the following code:
I have insured that this code is selecting only the visible cells from the range (the x variable counts how many it sees), and I have insured that they are the same addresses as what I am expecting based on the sort (I checked this by having it print test.Address). HOWEVER! When I run that last bit of code at the bottom to have the values print to the debugger window, IT'S NOT PRINTING THE 3 CELLS I HAVE VISIBLE!!!! It's printing the first visible cell and then the next 2 below it. NOT the other 2 cells that are in the range.
EX I have rows 3,7, and 64 visible. But it's printing 3,4 & 5
Why is it doing this?????? I suspect it has something to do with this line of code:
and if so could someone explain this line in laymans terms? I thought I understood it.
VBA Code:
Sub Filter_multi()
Dim lastCol As Variant
Dim lastRow As Variant
Dim cel As Variant
Dim test As Range
Set ItemRange = Application.InputBox("Select range of the Item/Component Column. Starting on row 2 of that column, drag it down to the last cell", "Obtain Range Object", Type:=8)
With ItemRange
.AutoFilter field:=1, Criteria1:=(Worksheets("Take Off...your INITIALS").Range("N9").Text) 'The value for this is FB74
.AutoFilter field:=5, Criteria1:=">=" & Worksheets("Take Off...your INITIALS").Range("U9").Value 'The value for this is 4
.AutoFilter field:=3, Criteria1:=(Worksheets("Take Off...your INITIALS").Range("S9").Text & "*") 'The value for this is Pipe
.AutoFilter field:=4, Criteria1:="<=" & Worksheets("Take Off...your INITIALS").Range("U9").Value 'The value for this is 4
x = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Count 'Counts how many visible rows are present after autofilter
MsgBox (x) 'Sanity Check
lastCol = ItemRange.Cells(1, Columns.Count).End(xlToLeft).Column 'Grabs the cells in the first row (header cells)
Set srcRow = ItemRange.Range("A1", ItemRange.Cells(1, lastCol))
Set found = srcRow.Find(What:="Material", LookAt:=xlWhole, MatchCase:=False) 'Looking for the column with the header named "Material"
lastRow = .Columns(found.Column).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select 'Selects only the visible rows in column I
Set test = Selection
test.Copy ' Copies Selection - had to make on separate line otherwise would throw "Object Required" error
MsgBox ("Test has " & test.Count & " Cells") 'Right here is where I'm losing my mind
For iNum = 1 To x
Debug.Print test(iNum, 1)
Next iNum
I have insured that this code is selecting only the visible cells from the range (the x variable counts how many it sees), and I have insured that they are the same addresses as what I am expecting based on the sort (I checked this by having it print test.Address). HOWEVER! When I run that last bit of code at the bottom to have the values print to the debugger window, IT'S NOT PRINTING THE 3 CELLS I HAVE VISIBLE!!!! It's printing the first visible cell and then the next 2 below it. NOT the other 2 cells that are in the range.
EX I have rows 3,7, and 64 visible. But it's printing 3,4 & 5
Why is it doing this?????? I suspect it has something to do with this line of code:
VBA Code:
lastRow = .Columns(found.Column).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select