Excel Autofilter

[lucotuslim]

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.
--
Lucotus

[David]

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,

"lucotuslim" wrote:

> 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.
> --
> Lucotus

[lucotuslim]

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

--
Lucotus


"David" wrote:

> 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,
>
> "lucotuslim" wrote:
>
> > 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.
> > --
> > Lucotus

[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

lucotuslim wrote:
>
> 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.
> --
> Lucotus

--

Dave Peterson