Delete from Sheet1 based on value in Sheet2

I have the attached example of a file where I need to

do the below:

Delete rows to end of used rows on Sheet1 where
Sheet1 column A matches Sheet2 column C
And Sheet2 column AP is not null

Can someone please help on how best to approach this either using a formula or VBA - preferably vba.  

Thanks in advance.
Not-Null.zip
leezacAsked:
Who is Participating?
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Hi again,

Thanks for your confirmation.

This code should meet your requirements:

Option Explicit
Public Sub Q_28133386()

  Dim lngRow                                            As Long
  Dim objCell                                           As Range
  Dim objRange                                          As Range
  
  On Error Resume Next
  
  Application.ScreenUpdating = False
  
  Set objRange = Range(Worksheets("Sheet2").[C3], Worksheets("Sheet2").Cells(Worksheets("Sheet2").Cells.Rows.Count, "C").End(xlUp))
  
  If Not (objRange Is Nothing) Then
     Worksheets("Sheet1").Select
  
     For lngRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row To 2& Step -1&
  
         Set objCell = Nothing
         Set objCell = objRange.Find(What:=Cells(lngRow, "A"))
      
         If Not (objCell Is Nothing) Then
            If Not (IsEmpty(Worksheets("Sheet2").Cells(objCell.Row, "AP"))) Then
               Worksheets("Sheet1").Rows(lngRow).EntireRow.Delete
            End If ' If IsEmpty(Worksheets("Sheet2").Cells(objCell.Row, "AP")) Then
         End If ' If Not (objCell Is Nothing) Then
         
     Next lngRow
  End If ' If Not (objRange Is Nothing) Then
  
  Set objCell = Nothing
  Set objRange = Nothing
  
  Application.ScreenUpdating = True
  
End Sub

Open in new window


I have also attached a workbook based on your original with this code included within a separate code module, "basQ_28133386", for convenience.

BFN,

fp.
Q-28133386.xls
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Just to confirm your expected outcome; in the example workbook provided, there would just be two remaining rows in [Sheet1]:

flour
soda


...or would "Milk" still be present (as it does not match "milk" in [Sheet2])?

Thanks.

BFN,

fp.

PS. Should "beens" read "beans"? :)
0
 
leezacAuthor Commented:
expected outcome;  there would just be two remaining rows in [Sheet1]:

flour
soda

Thanks
0
 
leezacAuthor Commented:
Thank you - works perfect
0
 
[ fanpages ]IT Services ConsultantCommented:
You are very welcome.

Good luck with the rest of your project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.