Remove Duplicates - runtime error 91 - Don't know how to fix.

[mysterious-dr-x]

It occures to me that using & learning vba concurrently is probably a bad idea. Now, however, it is far too late for that. My main issue is that I don't know how to fix any errors that turn up & I need a little help.

I'm trying to set up a little macro in excel 2007 that will determine where the end of the data is, select it all, then remove any duplicates. (This will autorun after additional data has been added to the range, although that is not currently coded.)

The code below is what I have at this time, & the debugger claims that the error is occuring on the "LR = .....etc" row.

I admit, this code has partially been created with the "record macro" function, & I know that there may be a better way. Please don't hold it against me.

Dim LR As Long
Dim ws As Worksheet
 
    LR = ws.Range(ws.Rows.Count).End(xlUp).Row
    Rows("9:" & LR).Select
    ActiveSheet.Range("9:" & LR).RemoveDuplicates Columns:=Array(1, 2, 3, 4, _
         5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, _
         24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37),
         Header:=xlNo
 
End Sub

 

[Jerry Sullivan]

In the example below, I started with the even number rows with fill color blue and bold font.

Excel Workbook
	A	B	C	D
9	1	2	3	4
10	Duplicate	Duplicate	Duplicate	Duplicate
11	8	1	10	1
12	4	5	1	1
13	Duplicate	Duplicate	Duplicate	Duplicate
14	6	2	1	8
15	2	1	8	4
16	Duplicate	Duplicate	Duplicate	Duplicate
17	5	1	1	4
Before

After running the code all "Duplicates" except the first have been deleted and the rows shifted up.

Excel Workbook
	A	B	C	D	E
9	1	2	3	4	5
10	Duplicate	Duplicate	Duplicate	Duplicate	Duplicate
11	8	1	10	1	5
12	4	5	1	1	4
13	6	2	1	8	4
14	2	1	8	4	7
15	5	1	1	4	9
After

Do you get a different result?

 

[mysterious-dr-x]

Do you get a different result?

I do. May be it's because I have borders not shading, but they disappear & leave a gap.


-- removed inline image ---


In fact, I just tested it with some background colour, & that disappears too.