Using Data From Select Range

nmbc99

New Member
Joined
Apr 28, 2022
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hello! I have created the following code:

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
and if so could someone explain this line in laymans terms? I thought I understood it.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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:
VBA Code:
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.
 
Last edited:
Upvote 0
Solution
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:
VBA Code:
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:


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

Forum statistics

Threads
1,223,880
Messages
6,175,157
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top