VBA Script Help Is this possible

[MikeExcelNovice]

I use the below script at work to easily and quickly concatenate large amounts of data. We use this to create a common denominator for vlookups between reports. I want to see if I can make this report as automated as possible. The script reads as follows:

Sub combine_columns()
Dim store As Variant
Dim sku As Variant
Range("D1").Select 'Change as appropriate
For Each sku In Range("B1:B") 'Change as appropriate
For Each store In Range("A1:A") 'Change as appropriate
ActiveCell.Formula = store & sku
ActiveCell.Offset(1, 0).Select
Next
Next
Range("D1").Select
End Sub

All I have to do is paste my data into columns A and B. Once that is complete I go into VBA and update the range to read B1:B6 and A1:A155 for example. Once I run the macro the concatenation populates in column D. To save time I wrote a COUNT formula in G2 to know my total range for A and a COUNT formula in H2 to know my total range for column B. Is there anyway to insert these values into the macro so I don't have to manually type them in every time.


For example, if my range in column A is A1:A64, cell G2 will read A64 so I know to go into the macro and edit A1:A(blank) to A1:A64.


Thanks for any help!!!!!

 

[Leith Ross]

Hello MikeExcelNovice,

This version of your macro will automatically detect the end of both columns "A" and "B" and place the concatenated result in column "D".

Sub combine_columns()


    Dim rngOut      As Range
    Dim rngSKU      As Range
    Dim rngStore    As Range
    Dim store       As Variant
    Dim sku         As Variant
    
        Set rngStore = Range("A1", Cells(Rows.Count).End(xlUp))
        Set rngSKU = Range("B1", Cells(Rows.Count).End(xlUp))
        
        Set rngOut = Range("D1")
        
            For Each sku In rngSKU
                For Each store In rngStore
                    rngOut = store & sku
                    Set rngOut = rngOut.Offset(1, 0)
                Next store
            Next sku
            
        Range("D1").Select


End Sub

 

[Fluff]

Hi & welcome to the board
Try changing these 2 lines as shown

For Each sku In Range("B1",Range("B")&Rows.Count).End(xlup)) 'Change as appropriate
    For Each store In Range("A1",Range("A"&Rows.count).End(xlup)) 'Change as appropriate

 

[JLGWhiz]

another version

Sub combine_columns()
 Dim store As Variant, cnt1 As Long, cnt2 As Long
 Dim sku As Variant
 cnt1 = Cells(Rows.Count, 1).End(xlUp).Row
 cnt2 = Cells(Rows.Count, 2).End(xlUp).Row
 For Each sku In Range("B1:B" & cnt2) 'Change as appropriate
    For Each store In Range("A1:A" & cnt1) 'Change as appropriate
        If Range("D1") = "" Then
            Range("D1") = store & sku
        Else
            Cells(Rows.Count, 4).End(xlUp)(2) = store & sku
        End If
        Range("G2") = cnt1
        Range("H2") = cnt2
    Next
 Next
 Range("D1").Select
 End Sub

 

[MikeExcelNovice]

Hi & welcome to the board
Try changing these 2 lines as shown

For Each sku In Range("B1",Range("B")&Rows.Count).End(xlup)) 'Change as appropriate
    For Each store In Range("A1",Range("A"&Rows.count).End(xlup)) 'Change as appropriate

Thanks for the quick response. I've tried changed the 2 lines but keep getting a compile error saying Expected:End of statement.

 

[Fluff]

Oops, had an extra ) in there. It should be

   For Each sku In Range("B1", Range("B" & Rows.Count).End(xlUp)) 'Change as appropriate
      For Each store In Range("A1", Range("A" & Rows.Count).End(xlUp)) 'Change as appropriate

 

[MikeExcelNovice]

Oops, had an extra ) in there. It should be

   For Each sku In Range("B1", Range("B" & Rows.Count).End(xlUp)) 'Change as appropriate
      For Each store In Range("A1", Range("A" & Rows.Count).End(xlUp)) 'Change as appropriate

That did the trick. Thanks everyone all 3 answers worked.

 

[Fluff]

Glad we could help & thanks for the feedback