Removing Lines Identified by Autofilter
 

I have a large sheet that uses an ODBC connection to a
large database to pull back approx 14000 lines. I then
have to do some filtering in Excel which I do manually
via the Autofilter then highlight the lines and hit Ctrl -
(minus key). Does anyone know the code to delete the rows
that are returned by the autofilter ?

I tried recording it as a macro, the autofilter part is
OK, I can dim an array put in the values I want removed
then loop the autofilter so each value in the array is
filtered, but the lines removal records as a fixed range,
I want the range to be dynamic based on the results of
the filter....

Many thanks in advance

Removing Lines Identified by Autofilter
 

Could you send an example of the code you have so far?

"Neil" wrote in message
...
I have a large sheet that uses an ODBC connection to a
large database to pull back approx 14000 lines. I then
have to do some filtering in Excel which I do manually
via the Autofilter then highlight the lines and hit Ctrl -
(minus key). Does anyone know the code to delete the rows
that are returned by the autofilter ?

I tried recording it as a macro, the autofilter part is
OK, I can dim an array put in the values I want removed
then loop the autofilter so each value in the array is
filtered, but the lines removal records as a fixed range,
I want the range to be dynamic based on the results of
the filter....

Many thanks in advance

Removing Lines Identified by Autofilter
 

One way:

Option Explicit
Sub testme()
Dim Rng As Range
Dim RngF As Range

With Worksheets("sheet1")
If .AutoFilterMode = False Then
MsgBox "please apply a filter"
Exit Sub
End If
If .FilterMode = False Then
MsgBox "You haven't filtered anything!"
Exit Sub
End If
Set Rng = .AutoFilter.Range

On Error Resume Next
With Rng
Set RngF = .Offset(1, 0).Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If RngF Is Nothing Then
MsgBox "filtered, but no match"
Else
RngF.EntireRow.Delete
.ShowAllData
End If
End With
End Sub




Neil wrote:

I have a large sheet that uses an ODBC connection to a
large database to pull back approx 14000 lines. I then
have to do some filtering in Excel which I do manually
via the Autofilter then highlight the lines and hit Ctrl -
(minus key). Does anyone know the code to delete the rows
that are returned by the autofilter ?

I tried recording it as a macro, the autofilter part is
OK, I can dim an array put in the values I want removed
then loop the autofilter so each value in the array is
filtered, but the lines removal records as a fixed range,
I want the range to be dynamic based on the results of
the filter....

Many thanks in advance

--

Dave Peterson