Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
Hi Ron,
It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
You need the other two filter lines also
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Ron, It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
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" wrote: You need the other two filter lines also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Ron, It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
Hi Albert
I never work with a DTPicker1. But if you send me your test file private I will look at it this weekend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: You need the other two filter lines also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Ron, It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
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" wrote: Hi Albert I never work with a DTPicker1. But if you send me your test file private I will look at it this weekend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: You need the other two filter lines also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Ron, It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: Hi Albert I never work with a DTPicker1. But if you send me your test file private I will look at it this weekend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: You need the other two filter lines also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Ron, It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
To what address must I send it? I left your mail address at work
"Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: Hi Albert I never work with a DTPicker1. But if you send me your test file private I will look at it this weekend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: You need the other two filter lines also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Ron, It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
Hi Rin,
How do I get the calender control as a pop up in a userform? "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: Hi Albert I never work with a DTPicker1. But if you send me your test file private I will look at it this weekend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: You need the other two filter lines also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Ron, It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
See
http://www.rondebruin.nl/ -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... To what address must I send it? I left your mail address at work "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: Hi Albert I never work with a DTPicker1. But if you send me your test file private I will look at it this weekend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: You need the other two filter lines also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Ron, It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter - delete filtered selection
Same way as the datepicker
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Rin, How do I get the calender control as a pop up in a userform? "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: Hi Albert I never work with a DTPicker1. But if you send me your test file private I will look at it this weekend -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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" wrote: You need the other two filter lines also -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... Hi Ron, It only seems to be filtering on one criteria? Have I entered the code correctly? Thanks Albert "Albert" wrote: 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 "Ron de Bruin" wrote: 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) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Albert" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter-Filtered List Scan | Excel Programming | |||
Copy Selection - Paste Selection - Delete Selection | Excel Programming | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
How do i view only the filtered results from autofilter ? | Excel Discussion (Misc queries) | |||
Selecting Filtered records only when AutoFilter is on | Excel Programming |