Forum Discussion
List duplicates and move entire row
Hi, Is there a formula in Excel that causes an entire row to move based on a command/formula for a particular cell in a row?
I have a sheet where column A contains a number/code in each cell w...
Of course! Thank you for your reply
Sub Listduplicates()
'Updateby Extendoffice 20160613
Dim rngA As Range
Set rngA = Range([E1], Cells(Rows.Count, "E").End(xlUp))
rngA.Offset(0, 1).Columns.Insert
With rngA.Offset(0, 1)
.FormulaR1C1 = _
"=IF(ISNA(MATCH(RC[-1],C[1],0)),"""",INDEX(C[1],MATCH(RC[-1],C[1],0)))"
.Value = .Value
End With
End Sub
Try this:
Sub SortEm()
Dim w As Worksheet
Dim m As Long
Dim c As String
Set w = ActiveSheet
m = w.Range("A" & w.Rows.Count).End(xlUp).Row
c = Application.TextJoin(",", True, w.Range("A1:A" & m))
With w.Sort
.SortFields.Clear
.SortFields.Add _
Key:=w.Range("D1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
CustomOrder:=CVar(c), _
DataOption:=xlSortNormal
.SetRange w.Range("D1:AN" & m)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub- Thank you so much for your reply and help, I could not make it work the way I wanted it to, but I will try a bit more 🙂