About Us

Jeff B[_2_]

--
Jeff B

"Norman Jones" wrote:

Hi Jeff,

field:=2, _

Should be:

field:=3, _

---
Regards,
Norman

"Norman Jones" wrote in message
...
Hi Jeff,

Try something like:

Public Sub Tester02C()
Dim rng As Range
Dim rng1 As Range
Dim destRng As Range
Dim srcSh As Worksheet
Dim destSh As Worksheet

With ActiveWorkbook
Set srcSh = .Sheets("Index")
Set destSh = .Sheets("Call First")
Set destRng = destSh.Range("A10") '<<=== CHANGE
End With

srcSh.Cells.AutoFilter _
field:=2, _
Criteria1:="CF"

Set rng = srcSh.AutoFilter.Range
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)

On Error Resume Next
Set rng1 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng1 Is Nothing Then
rng1.Copy Destination:=destRng
End If

srcSh.Cells.AutoFilter

End Sub

---
Regards,
Norman

"Jeff B" wrote in message
...
I've run into a quirky situation. I have a row of labels and then my data
below that. I'm using Autofilter to select rows below the labels meeting
certain criteria then copying them to another place. It works fine unless
I
have no rows meeting my criteria. Then I wind up copying the non-filtered
rows instead. Any help would be appreciated. (Range(strEmptyCell finds
the
first empty row to copy to.) I'm using the code below:

Sheets(Index).Select
Sheets(Index).Range("a1").AutoFilter _
field:=3, Criteria1:="CF*"
rngCopyRange.Offset(1, 0).Resize(rngCopyRange.Rows.Count - 1, _
rngCopyRange.Columns.Count).Copy

Sheets("Call First").Activate
Range(strEmptyCell).PasteSpecial (xlPasteValues)
Range("A1").Select
Sheets(Index).Activate

Set rngCopyRange = ActiveCell.CurrentRegion
rngCopyRange.Offset(1, 0).Resize(rngCopyRange.Rows.Count - 1, _
rngCopyRange.Columns.Count).Delete
Range("a1", "C1").AutoFilter 'Filter OFF
--
Jeff B

Thanks, I'll give it a try....

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Hpw do I delete multiple empty rows found between filled rows?	Bill	Excel Worksheet Functions	2	November 15th 09 07:12 PM
Excel copy only visible rows from autofilter	duketter	Excel Discussion (Misc queries)	2	June 30th 08 10:55 PM
In Excel, How do I get AutoFilter to provide# of records found?	DougD	Excel Discussion (Misc queries)	1	June 19th 08 07:51 PM
Copy Rows found using Find All feature	Scott H	Excel Discussion (Misc queries)	3	May 2nd 05 06:04 PM
how to display the count of lines found by autofilter?	elaineb	Excel Discussion (Misc queries)	1	April 6th 05 07:08 PM

Menu

About Us