Results 1 to 18 of 18

Compare 2 Worksheets based on single criteria and then delete row

Threaded View

  1. #1
    Registered User
    Join Date
    04-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    28

    Compare 2 Worksheets based on single criteria and then delete row

    Sheet1 in test.xlsm includes 2 columns listing different fruits (column A) and type (Column B). I would like to delete rows that do not equal "Mango". The "Mango" criteria is listed in Sheet1 in Criteria.xlsm. Problem: everything is deleted but the 2nd row that has fruit "Grape". What should happen is that only rows that don't have the fruit "Mango" should be deleted.

    Here is my code and it is executed in Sheet1 in test.xlsm (Both files (test and Criteria) attached. Thank you so much!

    Sub DeleteDifferentWorkbook()

    Application.ScreenUpdating = False
    Dim iRow As Integer
    Dim lr As Long
    Dim List As Variant
    Dim y As Workbook

    lr = ThisWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Set y = Workbooks.Open("C:\Users\jojoshu\Desktop\VBA Test\Criteria.xlsm")
    List = y.Worksheets("Sheet1").Cells(2, "A").Value

    For iRow = lr To 1 Step -1
    If Cells(iRow, "A").Value <> List Then
    ThisWorkbook.Worksheets("Sheet1").Rows(iRow).Delete
    End If
    Next iRow

    Application.ScreenUpdating = True

    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare two excel workbooks (or two worksheets within a single workbook)
    By sergiozygmunt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2016, 03:11 AM
  2. [SOLVED] Delete Worksheets Based on Name from InputBox Criteria
    By d247 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2014, 01:45 AM
  3. Replies: 1
    Last Post: 11-09-2014, 06:04 PM
  4. [SOLVED] Copy column data from multiple worksheets to single worksheet based on header criteria
    By nateaskins in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-21-2014, 10:42 AM
  5. Delete multiple rows of data based on criteria in a single cell
    By slaga9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2011, 03:41 AM
  6. Compare worksheets and delete those that don't match
    By Hootie in forum Excel General
    Replies: 3
    Last Post: 07-21-2010, 02:09 PM
  7. [SOLVED] compare two worksheets and delete rows
    By mike.wilson8@comcast.net in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-26-2006, 07:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1