Help on deleting rows using Macros

[ashel]

I have two workbooks in the following fashion:

Workbook1/Sheet1 Workbook2/Sheet1
aaa aaa
bbb bbb
ccc
ddd
eee

I want the macro to delete the values in Workbook1/Sheet1/Col1 if they match
with the values ith Workbook2/Sheet1/Col1.

Thanks for your help.

 

[John]

havn't tested this thouroughly but it should work:

Sub Delete_If_Match()
Dim firstRow As Integer, lastRow As Integer
Dim firstRow2 As Integer, lastRow2 As Integer

'WB1 range
firstRow = 1
lastRow = 8

'WB2 range
firstRow2 = 1
lastRow2 = 3

For i = firstRow To lastRow
For j = firstRow2 To lastRow2
If Workbooks("WB1.xls").Worksheets("Sheet1").Cells(i,
1).Value = Workbooks("WB2.xls").Worksheets("Sheet1").Cells(j, 1).Value
Then
Workbooks("WB1.xls").Worksheets("Sheet1").Cells(i,
1).EntireRow.delete
i = i - 1
Exit For
End If
Next j
Next i
End Sub

 

[jchen]

Hi, do you know how to coding using VBA? I solution will use a lot of hand
coding. This is just a rough overview of that I would do.

For some tips:
Record a "Find" operation by column and modify it to take string value.
Use a Range object to memorize the current selected cell in WB2.
use a loop like this
Workbooks("WB2").Sheets("sheet1").RangeObject.select
do until RangeObject.FormulaC1R1 = ""
searchValue = RangeObject.FormulaC1R1
Workbooks("WB1").Sheets("sheet1").Range("A1").select
while Fine(searchValue) = true then Rows(cstr(activecell.row)
+""+cstr(activecell.row)).delete
Workbooks("WB2").Sheets("sheet1").RangeObject.select
RangeObject.row = RangeObject.row +1
loop