Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I type in the same box in excel without removing any lines | Excel Discussion (Misc queries) | |||
Zero Lines in Spreadsheets (removing) | Excel Worksheet Functions | |||
Removing zero value lines | Excel Worksheet Functions | |||
Removing lines and/or borders | Excel Discussion (Misc queries) | |||
Removing Print Lines | Excel Discussion (Misc queries) |