Solved Where do I insert the code in this post for it to work?

December 19, 2014 at 07:17:44
Specs: Windows 7
Post from 2009 by DerbyDad03 about sorting pairings in Excel:


Assuming your data is already sorted so that the duplicates follow one another as in your example, try this code:

What it does is check each item in column B (the bar code) and compare it to the item above it. If they match, it deletes both rows.

When you are deleting items from a list in Excel you have to start at the bottom and work up because if you delete a row on the way down, the counter will increment and skip the row that was moved up after the deletion.

The error checking is used to handle the error that will occur when you reach Row 1. The code will try to check Row (1-1) (Row 0) and throw up an error. The code assumes that once it throws up the error, it must be at Row 1 and therefore done.

Sub DelDups()
'Determine the number of items in column B
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
On Error GoTo Done
'Loop through rows, starting at the bottom
For delRow = lastRow To 1 Step -1
'If duplicates are found, delete both rows
If Cells(delRow, 1) = Cells(delRow - 1, 1) Then
Cells(delRow, 1).EntireRow.Delete
Cells(delRow - 1, 1).EntireRow.Delete
End If
Next
Done:
End Sub


See More: Where do I insert the code in this post for it to work?

Report •


✔ Best Answer
December 19, 2014 at 09:18:58
With your cursor anywhere within the code you can click the green Run arrow in the VBE toolbar or choose Run from the VBE menu bar or press F5.

If you want to run the code from within your worksheet so you don't have to go into the VBE, you can use a command button or shape and assign the macro to the button so that it will run with a single click.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
December 19, 2014 at 08:23:54
While in your workbook, press Alt-F11 to open the VBE (Visual Basic Editor)

In the VBE, choose Insert...Module, then paste the code into the pane that opens.

Since there are no references to a specific sheet in the code, the code will run against the active worksheet.

I suggest that you test this code in a backup copy of your workbook since macros can not be undone.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 19, 2014 at 08:42:37
Thanks DerbyDad03 - I'll give it a try and see how I get on. I've been looking for a way to do this for ages so fingers crossed this will sort it out.

Happy Christmas.


Report •

#3
December 19, 2014 at 08:57:39
I've added the code in the right place but can't see how to activate it. Any clues?
Thanks

Report •

Related Solutions

#4
December 19, 2014 at 09:18:58
✔ Best Answer
With your cursor anywhere within the code you can click the green Run arrow in the VBE toolbar or choose Run from the VBE menu bar or press F5.

If you want to run the code from within your worksheet so you don't have to go into the VBE, you can use a command button or shape and assign the macro to the button so that it will run with a single click.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question