Filtering data based on two criteria's and then deleting those rows

[harvey121]

Hi all,

I'm searching for a macro wherein i can filter up to 5000 rows of data based on two conditions and then delete the filtered rows.

An example of what I'm trying to do is as follows:

A sample of my data looks like this:

ColA ColB ColC

Date		Market	Quantity
31-Jan		sweden	2
25-Nov		us	4
3-Jul		us	5
15-Aug		canada	7
17-Aug		canada	3
17-Nov		canada	6
21-Aug		sweden	8
25-Aug		us	3
28-Aug		us	6
28-Oct		us	5
28-Aug		us	1
26-Feb		sweden	3
31-Aug		sweden	4

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

Now I want to delete all those rows where the Market is Sweden and date is less than 21st March 2018, us and date less than 16th Oct 2018 canada and date less than 23rd Oct 2018.

PS: I'm getting the dates with the following format: 5/31/2018 11:23:51 PM but using short date over here.

Your help guys is really appreciated on this.

 

[steve the fish]

Try this:

Dim lr As Long
Dim arr As Variant

Application.ScreenUpdating = False

arr = Array("Sweden", DateSerial(2018, 3, 21), "US", DateSerial(2018, 10, 16), "Canada", DateSerial(2018, 10, 23))
With Sheets("Sheet1")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    If lr < 2 Then Exit Sub
    For i = LBound(arr) To UBound(arr) Step 2
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range("A1:C" & lr)
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:="<" & CDbl(arr(i + 1)) + 1
            .AutoFilter Field:=2, Criteria1:=arr(i)
            If .SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Offset(1, 0).Resize(lr - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
        End With
        .AutoFilterMode = False
    Next
End With

Application.ScreenUpdating = True

 

[harvey121]

I tried this but I'm getting an error staing that "No cells were found' and the data is filtered out showing no rows.

Try this:

Dim lr As Long
Dim arr As Variant

Application.ScreenUpdating = False

arr = Array("Sweden", DateSerial(2018, 3, 21), "US", DateSerial(2018, 10, 16), "Canada", DateSerial(2018, 10, 23))
With Sheets("Sheet1")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    If lr < 2 Then Exit Sub
    For i = LBound(arr) To UBound(arr) Step 2
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range("A1:C" & lr)
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:="<" & CDbl(arr(i + 1)) + 1
            .AutoFilter Field:=2, Criteria1:=arr(i)
            If .SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Offset(1, 0).Resize(lr - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
        End With
        .AutoFilterMode = False
    Next
End With

Application.ScreenUpdating = True

 

[steve the fish]

Which cells are the data in?

 

[steve the fish]

Ive just thought. Change this line:

If .SpecialCells(xlCellTypeVisible).Count > 1 Then

to

If .SpecialCells(xlCellTypeVisible).Count > 3 Then

because we are using A:C so three columns. If all that is left is the headers then the filters have filtered everything out and there are no visible cells hence the error.

 

[harvey121]

Hi Steve,
The error is resolved but none of the rows are getting deleted.

My code looks like this:

Sub Macro1()



Dim lr As Long
Dim arr As Variant


Application.ScreenUpdating = False


arr = Array("sweden", DateSerial(2018, 3, 21), "us", DateSerial(2018, 10, 16), "canada", DateSerial(2018, 10, 23))
With Sheets("Sheet1")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    If lr < 2 Then Exit Sub
    For i = LBound(arr) To UBound(arr) Step 2
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Range("A1:C" & lr)
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:="<" & CDbl(arr(i + 1)) + 1
            .AutoFilter Field:=2, Criteria1:=arr(i)
            If .SpecialCells(xlCellTypeVisible).Count > 3 Then
                .Offset(1, 0).Resize(lr - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
        End With
        .AutoFilterMode = False
    Next
End With


Application.ScreenUpdating = True


End Sub

My data looks as follows:
https://ibb.co/QY4m397

 

[steve the fish]

Its going to be the dates more than likely. If you click a cell with a date in it what is seen in the formula bar?

 

[harvey121]

The structure of my data looks like this:https://ibb.co/n37Ksfj

 

[harvey121]

This is the format : 1/31/2018 11:23:51 PM

Its going to be the dates more than likely. If you click a cell with a date in it what is seen in the formula bar?

 

[steve the fish]

Yeah formatting and the true value can be, and often is, different with dates. What does:

=ISNUMBER(A2)

produce? With A2 housing a date.