Autofilter - delete filtered selection

[Albert]

Hi Guys,

I have been using the following code (courtesy of Ron debruin):

Sub Copy_With_AutoFilter1()
Dim ws As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
Dim sourceWB As Workbook

With Application
.ScreenUpdating = False
.EnableEvents = False
End With


If bIsBookOpen("Test DB.xlsm") Then
Set destWB = Workbooks("Test DB.xlsm")
Else
Set destWB = Workbooks.Open("K:\Customer services screen\Test
Database\Test DB.xlsm")
End If

Set ws = destWB.Sheets("Sheet1")


Set rng = ws.Range("A1:ab" & Rows.Count)
FieldNum = 1
ws.AutoFilterMode = False

On Error Resume Next
Application.DisplayAlerts = False
Sheets("MyFilterResult").Delete
Application.DisplayAlerts = True
On Error GoTo 0

rng.AutoFilter Field:=3, Criteria1:="=" & ComboBoxCustomerAgent.Value
rng.AutoFilter Field:=14, Criteria1:="=" & DTPicker1.Value
rng.AutoFilter Field:=18, Criteria1:="= Open"

Set WSNew = Workbooks("Customer services test.xlsm").Worksheets("Sheet2")

ws.AutoFilter.Range.Copy

With WSNew.Range("A1")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
'
Application.CutCopyMode = False
TextBoxWorkfortoday.Text = Cells(Rows.Count, 1).End(xlUp).Row - 1

End With
'
With ws.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then rng2.EntireRow.Delete
End With

ws.AutoFilterMode = False
destWB.Close SaveChanges:=True
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Workbooks("Customer services test.xlsm").Worksheets("Sheet1").Activate
End Sub

I have 2 questions:
The autofilter is not filtering on all criteria?
And then not deleting those records that were filtered?

Any help?

Thanks
Albert

 

[Ron de Bruin]

hi Albert

Working with = in Autofilter can give problems if you filter on one date

Try this

rng.AutoFilter Field:=14, Criteria1:=">=" & CLng( DTPicker1.Value), _
Operator:=xlAnd, Criteria2:="<=" & CLng( DTPicker1.Value)

 

[Albert]

Hi Ron,

It does not seem to work. Any other ideas. I will try during the day and let
you know to my progress.

Thanks
Albert

 

[Albert]

Hi Ron,

It only seems to be filtering on one criteria? Have I entered the code
correctly?

Thanks
Albert

 

[Ron de Bruin]

You need the other two filter lines also

 

[Albert]

I have tried the extra lines, but still no luck.

I have substituted your code for autofilter field=14

It returns nothing if all filters are in place. If I block out field 14 and
18 it works but not if all 3 are active

 

[Ron de Bruin]

Hi Albert

I never work with a DTPicker1.
But if you send me your test file private I will look at it this weekend

 

[Albert]

Hi Ron,

I am using VB at the back of excel 2007. The datepicker I used can be found
by:
Openning the toolbox
Right clicking on it
And choosing the datepicker.

I am have problems with the value of the datepicker, so perhaps its the
wrong tool to use? Is there code I can get to pop up a month calender then
input the date and that becomes the value of the textbox and in return
populates the spreadsheet.

Thanks
Albert

 

[Ron de Bruin]

I know where it is but I always use a Calendar control

If you want I look at it for you, send me your testfile then

 

[Albert]

To what address must I send it? I left your mail address at work

 

[Albert]

Hi Rin,

How do I get the calender control as a pop up in a userform?

 

[Ron de Bruin]

See
http://www.rondebruin.nl/

 

[Ron de Bruin]

Same way as the datepicker