VBA Code to Delete Rows of Data if Cells Contain the Same Value From Another Sheet

**Darrellcoholic** · 05-30-2014, 10:19 AM

Hello,

I am new to VBA and will like to seek help in writing a macro.
I have got a set of data in a workbook with Sheet 1 range A1 to K373 and a range of data in Sheet 2 ranging from A1 to A30. What I will like to do is to have a VBA code to loop through all the cells in Sheet 1 column C and check which are the cells which value matches those in Sheet 2. Those that matches in Sheet 1 will have the entire row deleted. I have come out with the following codes but it does not work and hope to have someone here to guide me through.

Sub Test()
Dim intcounter As Integer
Dim y As String
intcounter = 1
Set y = Worksheets("Sheet2").Ranges("A1:A30")
For intcounter = 2 To 373
If Cells(intcounter, 3).Value = "y" Then
Cells(intcounter, 3).EntireRow.Delete
x = x + 1
 
End If
Next
 
End Sub

**JOHN H. DAVIS** · 05-30-2014, 10:33 AM

Please use code tags when posting.

Maybe:

Sub Darrellcoholic()
Dim i As Long
Columns(4).Insert
With Range("D2:D" & Range("C" & Rows.count).End(3)(1).Row)
    .Formula = "=VLOOKUP(C2,Sheet2!$A$2:$A$" & Sheets("Sheet2").Range("A" & Rows.count).End(3)(1).Row & ",1,FALSE)"
    .Value = .Value
    .Replace "#N/A", ""
End With
For i = Range("C" & Rows.count).End(3)(1).Row To 2 Step -1
    If Range("D" & i).Value <> "" Then Rows(i).Delete
Next i
Columns(4).Delete
End Sub

**Darrellcoholic** · 05-31-2014, 10:22 AM

Hey John,

Got it. Thank you so much, it worked!

**JOHN H. DAVIS** · 06-02-2014, 06:15 AM

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

VBA Code to Delete Rows of Data if Cells Contain the Same Value From Another Sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Code to Delete Rows of Data if Cells Contain the Same Value From Another Sheet

Re: VBA Code to Delete Rows of Data if Cells Contain the Same Value From Another Sheet

Re: VBA Code to Delete Rows of Data if Cells Contain the Same Value From Another Sheet

Re: VBA Code to Delete Rows of Data if Cells Contain the Same Value From Another Sheet

Thread Information

Users Browsing this Thread

Similar Threads

Changing code from deleting rows to cut/paste rows into another sheet and delete blank row

[SOLVED] Delete rows with #N/A values on my sheet using VBA code

[SOLVED] Code to quickly delete rows in sheet

Code to delete rows containing names from a list on another sheet

Code to delete rows and column cells that have formulas in.

Bookmarks

Bookmarks

Posting Permissions