Roger, thanks for helping me, but that didn't work either. I now get a #REF!
in Cell L1 and a zero in Cell L2 and Cell L3 with no "Duplicates" listed and
no sorting afterwards. Anything else you could think of would be appreciated.
Thanks
:
Hi Mike
Try
Sub Duplicates()
'
' Duplicates Macro
'
' Keyboard Shortcut: Ctrl+d
Dim lr As Long
lr = Cells(Rows.Count, "L").End(xlUp).Row
Columns("L
").Delete Shift:=xlToLeft
Range("L1") = "Duplicates"
Columns("L:L").EntireColumn.AutoFit
Range("L2").FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)"
Range("L2").Copy Range("L3:L" & lr)
Application.CutCopyMode = False
Range("L2:L" & lr) = Range("L2:L" & lr).Value
ActiveWorkbook.Worksheets("export(1)").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("export(1)"). _
Sort.SortFields.Add Key:=Range("L2:L" & lr), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("export(1)").Sort
.SetRange Range("A1:L" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
--
Regards
Roger Govier
MrMike wrote:
Thanks Roger, maybe you can help me fix the macro I recorded:
Sub Duplicates()
'
' Duplicates Macro
'
' Keyboard Shortcut: Ctrl+d
'
Columns("L
").Select
Selection.Delete Shift:=xlToLeft
Range("L1").Select
ActiveCell.FormulaR1C1 = "Duplicates"
Range("L2").Select
Columns("L:L").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)"
Selection.Copy
Range("L3:L300").Select
ActiveSheet.Paste
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Cells.Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("export(1)").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("export(1)").Sort.SortFields.Add Key:=Range( _
"L2:L300"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("export(1)").Sort
.SetRange Range("A1:L300")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Right now it goes to row 300 regardless of the amount of rows with data, I
want it to only deal with the rows that have data in the, because if I have
200 rows with data, this macro will list 100 rows of blank duplicates. Your
help is greatly appreciated. Perhaps, you could even make this macro even
better. Thanks again
:
Hi Mike
The following code assumes your headers are in row 1.
It counts the number of rows with entries in columnA, and the number of
columns with data in row 1.
It then sorts that selection by ColumnA Ascending followed by column B
ascending.
Adjust to suit
Sub SortData()
Dim lr As Long, lc As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 1), Cells(lr, lc)).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub
--
Regards
Roger Govier
MrMike wrote:
I recorded a macro that sorts data in a certiain way, however sometimes the
data is 100 rows and sometimes it's more or less. How can I record the macro
to go to the very last row with printable material within a certain column
regardless of how many rows there are? Or is there a quick way to adjust the
step in of the macro and re-program it there? Any help is greatly
appreciated.
Thanks
.
.
