Trying to run a macro to sort and remove duplicates from entire sheet

**sgmiller** · 08-31-2009, 08:03 AM

Hi, currently use this macro (see below) but occasionally I have more rows than 842. I could increase to 10000 and this would work but looking for a more automated macro that will automatically remove duplicates from all rows i.e. a macro that works out all rows.

Sub copyTab()
'
' copyTab Macro
'

'
Cells.Select
Selection.Copy
Sheets("filtered_data").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$J$842").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

**mrice** · 08-31-2009, 08:49 AM

Try

Please Login or Register  to view this content.

assuming that there are no intervening blank rows.

**JBeaucaire** · 08-31-2009, 08:51 AM

Hi SGMiller. Welcome. Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to edit that post above and put code tags around that code you used. (Like I did below...)

I don't use Excel 2007, but in 2003 I use this technique to spot the last row of data (LR) and use it in the range instructions:

Please Login or Register  to view this content.

**sgmiller** · 08-31-2009, 10:01 AM

Originally Posted by JBeaucaire

Hi SGMiller. Welcome. Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to edit that post above and put code tags around that code you used. (Like I did below...)

I don't use Excel 2007, but in 2003 I use this technique to spot the last row of data (LR) and use it in the range instructions:

Please Login or Register  to view this content.

Hi this worked perfectly. Many thanks for you prompt reply.

**JBeaucaire** · 08-31-2009, 10:24 AM

Great! If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)