Cut rows, find a specific cell and paste cut rows below that cell row

  • Hi Guys,


    I'm new to vba and macros and I'm trying to cut certain rows in the for loop and the find a specific value / cell and paste the cut rows below that cell in the same sheet. All this happens with the for loop as the list is dynamic.


    '----------16----------select all purple rows

    Range("A1").AutoFilter Field:=5, Criteria1:=RGB(204, 192, 218), Operator:=xlFilterCellColor

    LR = Range("A" & Rows.Count).End(xlUp).Row

    Range("E2:E" & LR).SpecialCells(xlCellTypeVisible).Select

    '----------Insert selection contents in an array

    Dim arMyArray() As Variant

    arMyArray = Selection.Value

    '--------------

    Range("A1").AutoFilter

    For Each elem In arMyArray

    a = Right(elem, 12)

    Range("A1").AutoFilter Field:=1, Criteria1:=a

    Range("A2:F" & LR).SpecialCells(xlCellTypeVisible).Select

    Selection.Cut

    Range("A1").AutoFilter
    Range("A2:F" & LR).Find(What:=elem).Select

    Selection.Offset(1).Insert Shift:=xlDown

    ---- Not sure How I could paste the cut rows below the selected cell.


    Thanks for the help!

  • It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Attached a Sample sheet, the data is mocked for confidentiality purpose. however the code should loop the purple rows and find relevant account numbers in column A, filter those records and paste them below the specific purple row.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Please attach a file which is more representative of what your actual data can look like.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi, while I was testing the above macro code with some other excel sheets I have being getting the "No cell found" error for some of the files and I observed the following pattern. When the purple row starts with the value "ZTF 000000005017" the code works perfectly fine but when the purple rows start with any other record, I get the "No cell found" error. I have attached a sample excel sheet where I receive the error.


    I tried debugging this but couldn't get fix it.


    Thanks and stay safe!

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.