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]

Hello and Welcome,

You need to assign a worksheet to your variable ws.

This statement just declares the variable ws is a worksheet object.

Dim ws As Worksheet

You need to add another statement like one of these two examples below...

Set ws = ActiveSheet

Set ws = Worksheets("Sheet1")

 

[mysterious-dr-x]

Appreciate the help, thank you.

 

[mysterious-dr-x]

Hello and Welcome,

You need to assign a worksheet to your variable ws.

So you're solution did work, but now it is having trouble with this part

LR = ws.Range(ws.Rows.Count).End(xlUp).Row

Any idea why?

 

[Jerry Sullivan]

So you're solution did work, but now it is having trouble with this part

LR = ws.Range(ws.Rows.Count).End(xlUp).Row

Any idea why?

Use this instead...

LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

 

[mysterious-dr-x]

Use this instead...

LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

Cheers.

EDIT: Ok, this is becoming ridiculous. Now that this is fixed it is now selecting row 9 upwards. I can change it so that it selects row 9 down to infinity, however given that I am counting rows, this is not the plan. What am I still doing wrong & are there any other obvious errors?

 

[Jerry Sullivan]

EDIT: Ok, this is becoming ridiculous. Now that this is fixed it is now selecting row 9 upwards. I can change it so that it selects row 9 down to infinity, however given that I am counting rows, this is not the plan. What am I still doing wrong & are there any other obvious errors?

Sorry that I didn't look at your question more holistically in my earlier reply.

It appears that you are trying to remove duplicates beginning at Row 9 and going down until you run out of data.

If that's correct, then you'll need to test to make sure that you actually have data below Row 9.

I'd also suggest that you use the .find method to find the last row. The .end(xlup) approach works well to find the last row of data in a single column, but .find will be better for your application just in case the last row of data in your worksheet is below the last row of data in Column A.

Try this code below, which should be more robust.

Option Explicit
Sub Remove_Duplicates()
    Dim ws As Worksheet
    Dim LR As Long, lTopRow As Long
    On Error GoTo ErrorHandler
    Set ws = ActiveSheet
    lTopRow = 9
    With ws
        LR = .Cells.Find(What:="*", After:=.Range("A1"), _
            LookIn:=xlFormulas, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False).Row
 
        If LR <= lTopRow Then
            MsgBox "There is less than one row below Row " &  lTopRow -1
            Exit Sub
        End If
        .Rows(lTopRow & ":" & 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 With
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error"
End Sub

 

[mysterious-dr-x]

Sorry that I didn't look at your question more holistically in my earlier reply.

That's ok, I should have thought to ask about the whole code earlier, & not just the error.

Thank you very much for that, I see what you did there & it works perfectly, as I'm sure you knew it would.

One last thing if you don't mind, is it possible to get it to remove the information but not the cell formatting?

 

[Jerry Sullivan]

One last thing if you don't mind, is it possible to get it to remove the information but not the cell formatting?

Could you clarify that?
Remove duplicates will delete the cells that are duplicated and the cells below will shift up.
Are you wanting the fill color or other formatting to stay in place in the rows that are deleted?

 

[mysterious-dr-x]

Could you clarify that?
Remove duplicates will delete the cells that are duplicated and the cells below will shift up.
Are you wanting the fill color or other formatting to stay in place in the rows that are deleted?

Ah, well its not doing that, either the cells are shifting from the left, or it is just deleting the information & formatting.