Deleting entire row.

**mangesh** · 09-27-2006, 10:09 AM

Has anybody know the macro for deleting entire row from worksheet if certain data in any cell of a row appears more than once. I want to keep only one row with the data and if it appears more than once the second row should get deleted.

For example.

say in row no. 5 in column A5 the data is "michael" in B5 the data is "John" and in row no.6 in column A6 the data is "michael" in B6 the data is "rosy".Here michael appears more than once, now i want to delete the entire row if michael appears more than once.

**jtp** · 09-27-2006, 10:28 AM

Give this a shot

Please Login or Register  to view this content.

This checks for any duplicates in Column A and deletes all but the first occurrence.

HTH
jtp

**jtp** · 09-27-2006, 10:47 AM

Sorry you need to replace this line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

**wiredwrx** · 11-07-2006, 04:30 PM

Thanks for the code. I have a question though,

I just started with VBA and MACROS, and want to keep all my macros in a workbook called Macros.

However, I want to run the macro in other workbooks.

When I tried that, I ran into an issue with the following line,

Set cellB = Worksheets("Sheet1").Range("A" & (cellA.Row + 1) & ":A1000").Find(what:=cellA.Value)

First, is there a way to change the ("sheet1) reference to something like ("currentsheet") where that means the currently active sheet.

Also, does the "& ":A1000")" in the line mean that it stops checking at row 1000. Is there a way to make this work till the end of the data?

thanks,
Michael

**MDubbelboer** · 11-07-2006, 04:37 PM

Originally Posted by wiredwrx

Thanks for the code. I have a question though,

I just started with VBA and MACROS, and want to keep all my macros in a workbook called Macros.

However, I want to run the macro in other workbooks.

When I tried that, I ran into an issue with the following line,

Set cellB = Worksheets("Sheet1").Range("A" & (cellA.Row + 1) & ":A1000").Find(what:=cellA.Value)

First, is there a way to change the ("sheet1) reference to something like ("currentsheet") where that means the currently active sheet.

Also, does the "& ":A1000")" in the line mean that it stops checking at row 1000. Is there a way to make this work till the end of the data?

thanks,
Michael

1. definitely. you can use activesheet to refer to the currently selected sheet

2. it does stop checking at a1000. you can statically change that value to a larger number or you could do it dynamically with a cell.specialcells(xllastcell).row

Deleting entire row.

LinkBack

Thread Tools

Rate This Thread

Display

Deleting entire row.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions