Excel Autofilter

[Guest]

A B
1 100
2 200
3 100

Example, I have the above records, after I used the Autofilter, with column
B = 100.

A B
1 100
3 100

How can I use VB to extract value from column A (visible) one by
one...returning value 1 & 3 only.

 

[Guest]

Hi,
When you say extract, what do you mean. Will a copy of the visible cells in
column A be ok, or do you need the values put in a variable? It is pretty
easy to copy and paste the values somewhere else, but it is harder to put the
values into variables.
Thanks,

 

[Guest]

Dear David, would like to put into variables...

 

[Dave Peterson]

How about something like this:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim rngF As Range
Dim myCell As Range
Dim iCtr As Long
Dim myArr() As Variant

Set wks = Worksheets("Sheet1")

With wks
If .AutoFilterMode = False Then
MsgBox "Please apply autofilter"
Exit Sub
End If
If .FilterMode = False Then
'maybe???
MsgBox "Please filter something!"
Exit Sub
End If

With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
'header row only
MsgBox "No details shown. Please try again"
Exit Sub
End If
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
ReDim myArr(1 To rngF.Cells.Count)
iCtr = 0
For Each myCell In rngF.Cells
iCtr = iCtr + 1
myArr(iCtr) = myCell.Value
Next myCell
End With
End With

'check the work
If iCtr = 0 Then
'do nothing, shouldn't happen here because I used "exit sub" lots
Else
For iCtr = LBound(myArr) To UBound(myArr)
MsgBox myArr(iCtr) & "--" & iCtr
Next iCtr
End If

End Sub