hi,

Thanks for the feedback - I'm pleased we could help :)

Here's a slight twist on Rick's vba approach using arrays which -may be
slightly- quicker if you have a lot of rows of data to modify.


Code:
--------------------
Option Explicit

Sub ConcatBandC_UsingArrays()
Dim x As Long, LastRow As Long
Dim TempArrB As Variant
Dim TempArrC As Variant
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range(Cells(2, 2), Cells(LastRow, 2))
'populate temporary arrays with data from the sheet
TempArrB = .Value2
TempArrC = .Offset(0, 1).Value2
'loop through the array & modify it (in memory)
For x = LBound(TempArrB) To UBound(TempArrB)
TempArrB(x, 1) = TempArrB(x, 1) & Format(TempArrC(x, 1), " 00000")
Next x
'write array back to the spreadsheet
.Value2 = TempArrB
End With
End With
End Sub

--------------------


Note that the last action of writing the array back to the range may
(?) be subject to the limitations of copying vba arrays to worksheet
ranges which are discussed in the below links:
'Daily Dose of Excel » Blog Archive » Writing To A Range Using VBA'
(http://tinyurl.com/yhfzqj8)
'XL: Limitations of Passing Arrays to Excel Using Automation'
(http://support.microsoft.com/kb/177991)

Rick, I know you've given a quick solution (like I did in my initial
post) but I think it's important to explicitly define range objects
(even if it is just to the active sheet) because this makes OP's aware
of where the changes will occur/what data will be used - whereas not all
OP's know that the default of "range(..." is to the active sheet esp if
they are also impacting other sheets within the code.

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144631