Using Data From Select Range

[nmbc99]

Hello! I have created the following 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:

lastRow = .Columns(found.Column).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select

and if so could someone explain this line in laymans terms? I thought I understood it.

 

[shknbk2]

You aren't doing anything wrong with selecting the cells. What isn't happening is taking into account the different Areas contained in the Selection. Every time there is a break in a continuous rectangular range of selected cells, selection breaks up the selections into Areas. By selecting cells 3, 7, and 64, you have 3 Areas. For a different selection, say 3, 4, and 64, there would be 2 areas.

As an experiment, select a block of cells with the cursor (multiple rows and columns). Then, while holding Shift, select one of the cells in the middle to de-select it. Notice the new "borders" that show up. You now have a selection Area for each one of the rectangular selectioned bordered areas.

Try this:

Dim area As Range
Dim areaCell As Range

For Each area In test.Areas
    For Each areaCell In area.Cells
        Debug.Print areaCell.Value
    Next
Next

You won't be able to use iNum as you have it because each area restarts its "home" cell (1,1). That's why you're getting 4 and 5 to show up. Even though Area 1 (cell 3) is just a single cell, you can still reference Cell(2,1) of the Area, which gets you to 4. It doesn't automatically jump to the next Area's cells when you reference a cell beyond the current Area.

 

[nmbc99]

You aren't doing anything wrong with selecting the cells. What isn't happening is taking into account the different Areas contained in the Selection. Every time there is a break in a continuous rectangular range of selected cells, selection breaks up the selections into Areas. By selecting cells 3, 7, and 64, you have 3 Areas. For a different selection, say 3, 4, and 64, there would be 2 areas.

As an experiment, select a block of cells with the cursor (multiple rows and columns). Then, while holding Shift, select one of the cells in the middle to de-select it. Notice the new "borders" that show up. You now have a selection Area for each one of the rectangular selectioned bordered areas.

Try this:

Dim area As Range
Dim areaCell As Range

For Each area In test.Areas
    For Each areaCell In area.Cells
        Debug.Print areaCell.Value
    Next
Next

You won't be able to use iNum as you have it because each area restarts its "home" cell (1,1). That's why you're getting 4 and 5 to show up. Even though Area 1 (cell 3) is just a single cell, you can still reference Cell(2,1) of the Area, which gets you to 4. It doesn't automatically jump to the next Area's cells when you reference a cell beyond the current Area.

Thank you shknbk2! I didn't realize select grabbed things by areas! That solved my problem! This could potentially help me with another question I had posted:

AutoFilter Results into a user Selection

Hello! I am trying to create a code that after an autofilter, if there are multiple results then some type of inquiry pops up to allow the user to choose the best option. For example after an autoFilter the following results are showing: Ms Excel TAKE OFF from Spec MOC ITEM Smaller Bigger...

www.mrexcel.com

If you have time/interest maybe you could take a look at it? No problem if you don't