Filter and Delete Rows from dataset based on Two Criteria

[PatrickW1907]

Hi,

I haven't got any code as of yet.

I have a range of data from A:AK this updates daily where the number of rows can increase/decrease per day

I am trying to delete rows where Column P is equalled to true and column AK = Today's date (File updates daily)

If the above condition is not met I would like to do nothing and continue with next steps of code.

Does anyone have any advice for best steps of practice I am new to VBA.

Kind Regards

Patrick Wood

 

[Joe4]

Can't you just use the new FILTER function to dynamically return the records you want in another list? This requires NO VBA.
See: FILTER function - Microsoft Support

 

[PatrickW1907]

Can't you just use the new FILTER function to dynamically return the records you want in another list? This requires NO VBA.
See: FILTER function - Microsoft Support

Hi Joe thanks for commenting. I've got other Macros on the go which the filtering will follow suit. The idea being I don't want the user to filter. As I am trying to clean the data set.

 

[Joe4]

Hi Joe thanks for commenting. I've got other Macros on the go which the filtering will follow suit. The idea being I don't want the user to filter. As I am trying to clean the data set.

Don't confuse the new FILTER worksheet function (new in 2019) with the old FILTER functionality that Excel has had for years.
It is a formula, so requires no user interaction! It really is very powerful, and makes things much more dynamic then they ever used to be.

If you use the TODAY() function as one of the criteria in the formula, it will ALWAYS apply the current date to the formula.
Try it and see!

 

[PatrickW1907]

Hi Joe this isn't suiting my needs. I just need to create vba code to complete the task alongside what i am running. Thank you for the alternative method though

 

[Joe4]

OK, another way would be to use a loop to do that.

Just a few questions:
- Are the True entries in column P TEXT values of True, or boolean values of TRUE (boolean values are typically in all caps and centered within the cell)?
- Are the dates on column AK dates only, or are there time components also involved?

 

[PatrickW1907]

OK, another way would be to use a loop to do that.

Just a few questions:
- Are the True entries in column P TEXT values of True, or boolean values of TRUE (boolean values are typically in all caps and centered within the cell)?
- Are the dates on column AK dates only, or are there time components also involved?

Boolean and dates only

 

[Joe4]

OK, this should do what you want:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False

'   Find last row in column P with data
    lr = Cells(Rows.Count, "P").End(xlUp).Row
    
'   Loop through all rows backwards, up to row 2
    For r = lr To 2 Step -1
'       Check values in column P and AK
        If Cells(r, "P") = True And Cells(r, "AK") = Date Then
            Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[shinigamilight]

If you can provide some sample data then we might be able to help you.

 

[PatrickW1907]

OK, this should do what you want:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False

'   Find last row in column P with data
    lr = Cells(Rows.Count, "P").End(xlUp).Row
   
'   Loop through all rows backwards, up to row 2
    For r = lr To 2 Step -1
'       Check values in column P and AK
        If Cells(r, "P") = True And Cells(r, "AK") = Date Then
            Rows(r).Delete
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

works a treat joe thank you !!