Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I type in the same box in excel without removing any lines Regina03 Excel Discussion (Misc queries) 2 April 9th 08 09:07 PM
Zero Lines in Spreadsheets (removing) CCSWFL Excel Worksheet Functions 1 June 22nd 07 02:23 AM
Removing zero value lines ccswfl Excel Worksheet Functions 0 June 21st 07 05:17 PM
Removing lines and/or borders mystical_ways Excel Discussion (Misc queries) 1 August 9th 05 07:35 PM
Removing Print Lines Alex Excel Discussion (Misc queries) 2 May 20th 05 01:49 PM


All times are GMT +1. The time now is 02:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"