Forum Discussion

anne_sofie's avatar
anne_sofie
Copper Contributor
Sep 05, 2023

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...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Sep 05, 2023

anne_sofie 

Could you post the code instead of a screenshot, please? Thanks in advance.

  • anne_sofie's avatar
    anne_sofie
    Copper Contributor
    Sep 05, 2023

    HansVogelaar 

    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

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Sep 05, 2023

      anne_sofie 

      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
      • anne_sofie's avatar
        anne_sofie
        Copper Contributor
        Sep 05, 2023
        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 🙂

Resources