Selecting a Range after Autofilter

[Mike Williams]

Hello,

I'm very very new to Excel VBA programming and I have a question in
regards to selecting Ranges after an Autofilter has been applied.

The user will filter the sheet down to their criteria and then I need
to loop through columns A and B and add the filtered data to an array.

Here the code I have so far:

Dim LastRow
Dim b()

Range("A65536").End(xlUp).Select
LastRow = ActiveCell.Row

b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value

The problem is that it's not selecting all the values. I'm assuming a
thick gray line between row numbers in an autofilter indicates a
separate range. It only pulls the data from the "first" range. How do I
select all the ranges into one? and assign it to b?

 

[WhytheQ]

I suspect if autofilter is on then you are going to have to use
something like:

Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range

Regards
J

 

[Mike Williams]

Thanks but I don't quite understand what to do with that.

Let me explain about what I'm trying to do a little more.

Column A contains E-mail Addresses and Column B contains the e-mail
format for that e-mail address in Column A.

So the data looks like this

Column A
========
(e-mail address removed)

Column B
========
HTML

So when the end-user goes through and finishes filtering the data to
their specifications, they click a button and I need to write code that
gets the values from Column A and B from the visible data.

I had used

Dim LastRow
Dim arr1(), b() As Variant

Range("A65536").End(xlUp).Select
LastRow = ActiveCell.Row

b = Range("A2:B2" & LastRow).SpecialCells(xlCellTypeVisible).value

ReDim arr1(1 To UBound(b))

For i = 1 To UBound(b)
arr1(i) = ((b(i, 1) + b(i, 2)))
Next

This takes the values from Column A and B and concatenates them
together and adds them to the array. I then do further processing of
the array in another sub.

This only seems to work for the first "range" (not sure if thats the
correct term) because any data after the first thick gray line (under
the row number) doesn't show up in my code.

I'm sure the code you gave me would help me but I don't understand how
to apply it to what I already have.

Thanks for your help and the help for those who respond.

Mike

 

[Dave Peterson]

I'm not quite sure how your concatenating, but maybe this will help:

Option Explicit
Sub testme()

Dim rngF As Range
Dim rngV As Range
Dim myCell As Range
Dim myArr() As String
Dim iCtr As Long

With ActiveSheet
Set rngF = .AutoFilter.Range
If rngF.Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Cells.Count = 1 Then
MsgBox "only the header is shown"
Exit Sub
End If
End With

With rngF
'ignore the header from the count and come down one row
Set rngV = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

ReDim myArr(1 To rngV.Cells.Count)

iCtr = 0
For Each myCell In rngV.Cells
iCtr = iCtr + 1
myArr(iCtr) = myCell.Value & "," & myCell.Offset(0, 1).Value
Next myCell

End Sub

 

[Mike Williams]

Awesome Dave!

Thank you very much!

I think I'll have to get a Excel VBA reference guide or something. I
have no idea what resize does.

Thanks for your help!

Mike

 

[Dave Peterson]

VBA's help may help <bg>.

If you start with a range that's one cell and resize it to 12 rows by 9 columns:

Dim myRng as range
dim myCell as range

set mycell = activesheet.range("a1")
set myrng = mycell.resize(12,9)