Macro to sort across multiple sheets and remove duplicates

derek.hughes

Board Regular
Joined
Mar 16, 2012
Messages
53
I have a workbook with multiple sheets of data and all are in the same format (data with the same headers in columns A:I). However, the number of rows with data is a variable - different for each sheet.

I want it to sort column A ascending and column I descending, and then remove duplicates - for each sheet in the workbook. I keep getting a debug on this line:
".SetRange ActiveCell.Offset(-1, 0).Range("A1:I731")"

How do I resolve this? Thanks!

Derek




Code:
Sub Sort()'
' Sort Macro
'


'
Dim wsheet As Worksheet


For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
    ActiveCell.Cells.Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=ActiveCell.Range( _
        "A1:A730"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=ActiveCell.Offset( _
        0, 8).Range("A1:A730"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange ActiveCell.Offset(-1, 0).Range("A1:I731")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$1:$I$731").RemoveDuplicates Columns:=1, Header:=xlYes
    ActiveCell.Offset(-1, 0).Range("A1").Select


Next wsheet


End Sub
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
derek.hughes,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


IF I understand you correctly.


Sample worksheets before the macro:


Excel 2007
ABCDEFGHI
1ABCDEFGHI
251
342
453
524
615
7
Sheet1



Excel 2007
ABCDEFGHI
1ABCDEFGHI
231
322
433
5
Sheet2


After the macro:


Excel 2007
ABCDEFGHI
1ABCDEFGHI
215
324
442
553
6
7
Sheet1



Excel 2007
ABCDEFGHI
1ABCDEFGHI
222
333
4
5
Sheet2


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Sub Sort_V2()
' hiker95, 11/02/2014, ME815470
Dim ws As Worksheet, lr As Long
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
  lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
  ws.Range("A2:I" & lr).Sort key1:=ws.Range("A2"), order1:=1, key2:=ws.Range("I2"), order2:=2
  ws.Range("A2:I" & lr).RemoveDuplicates Columns:=1, Header:=xlYes
Next ws
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Sort_V2 macro.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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