Filtering results into array

Lavina

Board Regular
Joined
Dec 18, 2018
Messages
75
Hello guys,

im trying to filter some data and add it into an array

Code:
My filtering code is:
            If Sheets(sheetName).AutoFilterMode Then Sheets(sheetName).AutoFilter.ShowAllData
            lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
            lastRow = Cells(Rows.Count, 1).End(xlUp).Row
            Set filterRng = Range(Cells(1, 1), Cells(lastRow, lastCol))
            filterRng.Select
            isItEmpty = IsEmpty(filterRng)
            If isItEmpty = False Then filterRng.AutoFilter 5, SKUInQuesion
Once filtering is completed id like to save all the data into an array.

Code:
Dim FullDataArray() As Variant
Dim selectedRange As Range
lastRow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set selectedRange = Range("A1:Q" + CStr(lastRow)).SpecialCells(xlCellTypeVisible)
FullDataArray = selectedRange.Value

After i run that my array gets filled in with only the 1 row, the header row

BUT if i try to copy paste the selection it copies everything i want to

What am i doing wrong?
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
For now i will just copy paste it, push that into the array and remove the copy paste, but there has to be a better way
 
Upvote 0
After some stumbling around in the forum i found the solution:

Code:
Set rng = Range("A2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
Set selectedRange = Range("A1:Q" + CStr(lastRow)).SpecialCells(xlCellTypeVisible)
FullDataArray = rng.Value
FullDataArray2 = selectedRange.Value
rng and FullDataArray containing the things i need.
I'm still unsure why though, its the same range selection:
Code:
Cells(Rows.Count, "Q").End(xlUp).Row is equal to lastRow
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

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