Excel VBA - need to rollup sub products onto a single product line for editing

[Autumnscribe]

Hi All
Disclosure: I've cross posted this here but no solution yet.

VBA novice. Need to roll-up product lines to the SKU level. E.g. I've got:

Item_ID	Distribution Type	SKU number
HELPER ROW
5327978	DC	673386
5327978	DSD	673386

And I want:

Item_ID	Distribution Type	SKU number
5327978	DC DSD	673386

I haven't been able to get a concat formula or the & built in concat working.

Sub ConCatToSKU()

'Note for testing - delete any rows past 20

Dim LastRow As Integer

Dim CurrentRow As Integer

LastRow = ActiveSheet.UsedRange.row - 1 + ActiveSheet.UsedRange.Rows.Count

CurrentRow = 2



Range("A:A").NumberFormat = "General"

      Do While CurrentRow <= LastRow

      ' Writes a flag in an unused cell I can use as an IF criteria

             If IsEmpty(Range("E" & CurrentRow).Value) = True Then

             Range("DA" & CurrentRow).Value = "ZZZZ"

                      If Range("DA" & CurrentRow).Value = "ZZZZ" Then

                            'I was just testing writing a formula into the helper row

                             Range("E" & CurrentRow).Formula = "=Concat(E3,E5)"

                             'I NEED TO HAVE THIS CONCAT FORMULA RUN FOR ALL THE UNIQUE VALUES IN COL E

                       End If

                 End If



CurrentRow = CurrentRow + 1

Loop

End Sub

 

[Snakehips]

@Autumnscribe I don't fully appreciate your intentions nor the data layout. However, assuming that you are happy to have hard values rather than formula ? and that Column E is Distribution Type? Then maybe code more like the below?

Sub ConCatToSKU()

'Note for testing - delete any rows past 20

Dim LastRow As Integer
Dim CurrentRow As Integer
Dim Helper as Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LastRow = Range("E" & Rows.Count).End(xlUp).row. ' Avoid using used range

CurrentRow = 2

Range("A:A").NumberFormat = "General"  '?????????

      Do While CurrentRow <= LastRow

             If IsEmpty(Range("E" & CurrentRow).Value) = True Then
                Helper = CurrentRow
                Range("D" & Helper).Value = Range("D" & Helper + 1).Value
                Range("F" & Helper).Value = Range("F" & Helper + 1).Value
             End If

                Range("E" & Helper).Value = Trim(Range("E" & Helper).Value) & " " & Range("E" & CurrentRow + 1).Value

        CurrentRow = CurrentRow + 1

Loop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

MRXLMAY21.xlsm
	D	E	F
1	ID	Type	SKU
2
3	5327978	DC	673386
4	5327978	DSD	673386
5	5327978	ABC	673386
6	5327978	XYZ	673386
7
8	5327999	TST	673399
9	5327999	TST2	673399
Sheet3

MRXLMAY21.xlsm
	D	E	F
1	ID	Type	SKU
2	5327978	DC DSD ABC XYZ	673386
3	5327978	DC	673386
4	5327978	DSD	673386
5	5327978	ABC	673386
6	5327978	XYZ	673386
7	5327999	TST TST2	673399
8	5327999	TST	673399
9	5327999	TST2	673399
Sheet3

Hope that helps.

 

[Autumnscribe]

Thanks SN much appreciated.
I got pulled on to another job this week. I ll go through your code on weekend and come back with questions.