Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help on deleting rows using Macros
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help on deleting rows using Macros
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 ashel wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help on deleting rows using Macros
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.sele ct do until RangeObject.FormulaC1R1 = "" searchValue = RangeObject.FormulaC1R1 Workbooks("WB1").Sheets("sheet1").Range("A1").sele ct while Fine(searchValue) = true then Rows(cstr(activecell.row) +""+cstr(activecell.row)).delete Workbooks("WB2").Sheets("sheet1").RangeObject.sele ct RangeObject.row = RangeObject.row +1 loop "ashel" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macros deleting rows | Excel Discussion (Misc queries) | |||
Deleting Macros | Excel Discussion (Misc queries) | |||
Deleting Rows using Macros | Excel Discussion (Misc queries) | |||
Converting formula to value and deleting rows using macros. | Excel Discussion (Misc queries) | |||
Deleting Macros | Excel Programming |