VBA: Remove rows based on date in cell in another worksheet

Declamatory

Active Member
Joined
Nov 6, 2014
Messages
319
Good afternoon,

I have a workbook with to worksheets. Sheet 1 has a list of items. In column P is the date of payment.

In Sheet 2 I have payment date in cell C25.

Can anybody help me with vba code that would remove the rows in sheet 1 that don't have the date in cell C25 in Sheet 2 in column P?

Thanks in advance for any help you can give me.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Declamatory

Active Member
Joined
Nov 6, 2014
Messages
319
This is code that I use to delete rows in another macro I run that deletes the rows that have "Buy" in column B. I just need to somehow do the same but rather than "Buy" looking at the contents of cell C25 in Sheet 2.
Code:
  Last = Cells(Rows.Count, "M").End(xlUp).Row
       
    For i = Last To 1 Step -1
        If (Cells(i, "B").Value) = "Buy" Then
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Try this:

Code:
  Last = Cells(Rows.Count, "M").End(xlUp).Row
       
    For i = Last To 1 Step -1
        If worksheets("Sheet2".range("C25").Value <> (Cells(i, "B").Value Then
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
 

Declamatory

Active Member
Joined
Nov 6, 2014
Messages
319
Thanks for replying. I got a compile error:

Expected: list separator or )

The error was in the line

If worksheets("Sheet2".range("C25").Value <> (Cells(i, "B").Value Then
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
sorry, that should be If worksheets("Sheet2").range("C25").Value <> (Cells(i, "B").Value Then
 

Declamatory

Active Member
Joined
Nov 6, 2014
Messages
319
Thanks for your help. I think there should have been another ) after .Value

I really appreciate it, thanks.
 

Forum statistics

Threads
1,136,339
Messages
5,675,199
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top