Delete entire row(macro correction)

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Delete entire row(macro correction)

Post by zyxw1234 »

Hi Experts,

Code: Select all

Sub test()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = Workbooks.Open("C:UsersWolfieeeStyleDesktop1.xls")
Set wb2 = Workbooks.Open("C:UsersWolfieeeStyleDesktop2.xlsx")
Set sh1 = wb1.Worksheets.Item(1)
Set sh2 = wb2.Worksheets.Item(2) '---> you put item(1), while the sheet in wb2 which has data is in sheet2.
Set rngWb1 = sh1.Range("B2", sh1.Range("B" & sh1.Rows.Count).End(xlUp))
Set rngWb2 = sh2.Range("A2", sh2.Range("A" & sh2.Rows.Count).End(xlUp))
sh1.Cells(1, 10).Value = "KEEP"

With sh1
For Each c1 In rngWb2
sym = c1.Value
num = c1.Offset(0, 1).Value
    For Each c2 In rngWb1
    tt = c2.Value
    If c2.Value = sym And c2.Offset(0, 7).Value = num Then _
    c2.Offset(0, 8).Value = "keep"
    Next
Next

.Range("A1").AutoFilter Field:=10, Criteria1:="="
.Range("A2", .Range("i" & .Rows.Count).End(xlUp)) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Cells.AutoFilter
End With

sh1.Cells(1, 10).EntireColumn.Delete
'wb1.Save
'wb1.Close
'wb2.Close


End Sub

this macro works perfect for this:If column I of 1.xls matches with column B of sheet2 of 2.xlsx then dont do anything & if not matched then delete entire row

little modification is there:If column B of 1.xls matches with column E of sheet1 of 2.xlsx then delete entire row & if not matched don't do anything
So i am looking for the macro for this



Thnx For the Help

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete entire row(macro correction)

Post by HansV »

Let's do this step by step.

The current macro looks at sheet 2 of 2.xlsx. You want to look at sheet 1 of 2.xlsx instead.
What do you think you have to change to do this?
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Delete entire row(macro correction)

Post by zyxw1234 »

Sure Sir
wb2.Worksheets.Item(1)

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete entire row(macro correction)

Post by HansV »

OK, that's one step. :thumbup:

rngWb2 is column A in this sheet. In the loop For Each c1 In rngWb2 ... Next, c1 is a cell in column A.
You use

num = c1.Offset(0, 1).Value

to look at the value in the column to the right, that is column B. You now want to look at column E instead of column B. So you have to change the offset 1 in c1.Offset(0, 1). What do you need to look at column E?
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Delete entire row(macro correction)

Post by zyxw1234 »

Set rngWb2 = sh2.Range("D2", sh2.Range("D" & sh2.Rows.Count).End(xlUp))


Or

num = c1.Offset(0, 4).Value

Set rngWb2 = sh2.Range("A2", sh2.Range("A" & sh2.Rows.Count).End(xlUp))


I Think
Last edited by zyxw1234 on 26 Jul 2020, 20:56, edited 1 time in total.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete entire row(macro correction)

Post by HansV »

No, that is not what I meant. You have to change the offset 1 in the line

num = c1.Offset(0, 1).Value

so that you refer to column E instead of column B. Remember that c1 is a cell in column A.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Delete entire row(macro correction)

Post by zyxw1234 »

num = c1.Offset(0, 4).Value

Set rngWb2 = sh2.Range("A2", sh2.Range("A" & sh2.Rows.Count).End(xlUp))

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete entire row(macro correction)

Post by HansV »

Yes, num = c1.Offset(0, 4).Value is correct.

In the loop For Each c2 In rngWb1 ... Next, c2 is a cell in column B on sheet 1.
You want to compare this cell to the value num.
Am I correct that you don't need the value sym anymore?
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Delete entire row(macro correction)

Post by zyxw1234 »

No it's a alphabet plus numbers also both
Example
Data will be like this
ADANI
20MICRONS
like this

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete entire row(macro correction)

Post by HansV »

The next change is to the line

If c2.Value = sym And c2.Offset(0, 7).Value = num Then

What do you think it has to be?
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Delete entire row(macro correction)

Post by zyxw1234 »

If c2.Value = sym And c2.Offset(0, 1).Value = num

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Delete entire row(macro correction)

Post by HansV »

That would compare the value in column C (1 cell to the right of c2) to num. If I understand the question in the first post correctly, it should be

If c2.Value = num Then
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Delete entire row(macro correction)

Post by zyxw1234 »

Give me some time
I will let u know HansV Sir
Do not solve this problem, I will resume this conversation after some time Sir