Move content from one row to another Macro

[not3nough]

Hi all, I was wondering if you could help me with this problem I have (I include an image for reference), I am trying to clean and sort a list of products that gives me a system that I can not change.

What I am trying to do is to copy the value enclosed in red (product group mark) from the header of my list to column "K" of the document in such a way that the value is filled, until it finds another header down and the copy is made again, I have no idea what macro to use for that function, it is a fairly large list (like 2000 items) but they are repeated all the time.

On the other hand, I would like to pass all the row enclosed in the green box, towards the previous row. But from column L, this should be done based on a certain "mini-" value as I indicated in the picture.

I have tried to do it manually, but there are too many products and I tend to make mistakes, I can't find the solution, if you could help me, it would be just great.

Unfortunately I don't know much about macros, so I am asking for your invaluable help.

Thank you very much in advance

 

[Coding4Fun]

Give this a shot

Sub Test()

Dim i As Long
Dim MyRowsUsed As Long
Dim MyCounter As Long

MyRowsUsed = Cells(Rows.Count, 1).End(xlUp).Row
MyCounter = 0

For i = 1 To MyRowsUsed
  
        If Range("C" & i).Value = "STOCK DSP" Then
        
            Range("K" & i + 2).Value = Range("B" & i).Value
            Range("L" & i + 2 & ":T" & i + 2).Value = Range("A" & i + 3 & ":J" & i + 3).Value
            MyCounter = 1
            
        ElseIf MyCounter = 4 And Range("C" & i).Value <> "STOCK DSP" Then
        
            Range("K" & i).Value = Range("K" & i - 2).Value
            Range("L" & i & ":T" & i).Value = Range("A" & i + 1 & ":J" & i + 1).Value
            MyCounter = 3
        
        Else
        
        MyCounter = MyCounter + 1
        
        End If
    
Next i

End Sub

 

[not3nough]

Thank you very much, however it does not work after a few rows, if it is not too much trouble, I have uploaded the complete file so you can review it.

The first sheet is the data as I have it, the second sheet is more or less how it should look, and on the third sheet I included a brief explanation.

If you can help me I would appreciate it very much!

LIST_PRODUCTS.xlsx

Zippyshare.com - Free File Hosting

www102.zippyshare.com

 

[Coding4Fun]

My Antivirus went crazy when I hit your filesharing link...

I cant/wont download your file, if you can clarify where it failed I can try to help further.

 

[bebo021999]

Hope it works:

Option Explicit
Sub test()
Dim lr&, i&, rng, stock As String, arr()
lr = Cells(Rows.Count, "A").End(xlUp).Row
ReDim arr(1 To lr, 1 To 3)
rng = Range("A1:C" & lr).Value
    For i = 1 To UBound(rng) - 1
        If rng(i, 3) Like "*STOCK*" Then
            stock = rng(i, 2)
        ElseIf Not IsEmpty(rng(i, 3)) Then
            arr(i, 1) = stock
            arr(i, 2) = rng(i + 1, 1)
            arr(i, 3) = rng(i + 1, 2)
        End If
    Next
Range("L1").Resize(lr - 2, 3) = arr
Range("L2:L" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub