Delete duplicate rows with exceptions

M

MightyHammer

New Member
Joined
Jul 4, 2007
Messages
3
Hi,

I'm trying to write a macro that will delete all rows where the value in Column in C is the same as the one above it BUT not if the value is a blank row. Here's what I have but it keeps erroring on me....can anyone tell me what I'm doing wrong. Thanks!

Do Until lastrow = 1
If Cells(lastrow, 3).Value <> "" Then
If Cells(lastrow, 3).Value = Cells(lastrow - 1, 3).Value Then
Rows(lastrow).Delete
End If
End If
lastrow = lastrow - 1
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Welcome to the Board.

You need to initialize lastrow to something and you need to put LOOP in. Try this:

Code: 
Dim lastrow As Integer

lastrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
Do Until lastrow = 1
If Cells(lastrow, 3).Value <> "" Then
If Cells(lastrow, 3).Value = Cells(lastrow - 1, 3).Value Then
Rows(lastrow).Delete
End If
End If
lastrow = lastrow - 1
Loop
 
Upvote 0
One more thing, do you want to delete both rows if it is the same as above or just one of the 2 matching rows?
 
Upvote 0
D'oh - forgot to include the Loop!!!!!! LOL

Many thanks for your help.

One other thing while I'm here....before I delete these duplicates I'm copying the data from one worksheet to another using the following code...

Sheets("Test Sheet").Range("A1:L" & lastrow).Value = Sheets("Leads to
load").Range("A1:L" & lastrow).Value

All works - except that I'd like it to bring the cell formats with the copy instead of just the data. Do I need to use PasteSpecial or something instead of just = Value?
 
Upvote 0
Yes I would probably do something like:

Sheets("Test Sheet").Range("A1:L" & lastrow).Copy
Sheets("Leads to load").Paste

You may have to modify this some. If there are formulas you will want to use PasteSpecial because there may be a problem crossing sheets, but not positive.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ashe
  • Solved
VBA to delete rows that DON'T contain certain text
Replies
4
Views
251
Joe4
Joe4
R
  • Solved
finding last row issue
Replies
3
Views
79
Fluff
Fluff
S
  • Question
VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met
Replies
1
Views
117
Tupe77
T
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
197
amoverton2
A
L
  • Question
Auto-Archive deleted Rows in Excel
2 3 4
Replies
30
Views
404
liljunga
L

Forum statistics

Threads
1,214,591
Messages
6,120,425
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top