AutoFilter VBA help.

C

chrisdarl

Hi i have a sheet it has the heading "Name" in column a. i want to mak
a macro that applies a autofilter to the whole of columm a. then retur
the values in the autofilter in column c.

Can anyone please help. I've been trying to do this for ages but canno
manage it.

Thanks Chris
 
C

chrisdarl

Thats not really what i am after, i am wanting to display the criteria
values in a empty column i.e. B


Thanks anyway, chris.
 
D

Dave Peterson

This may get you started:

Option Explicit
Sub testme01()

Dim rngF As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks

'remove any existing filter first
If .FilterMode Then
.AutoFilterMode = False
End If

.Range("a:a").AutoFilter field:=1, Criteria1:="Chris3"
Set rngF = .AutoFilter.Range

If rngF.Cells.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'no visible cells in filter (except header row)
Else
'clear out existing data in column C?
.Range("B:B").ClearContents
With rngF
.Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=.Range("B1")
End With
End If
.AutoFilterMode = False
End With

End Sub
 
Top