[Excel 2003]
Hi,

I have been reading VBA for dummies and have been having trouble
trying to accomplish some simple vba tasks.

I have a report that is generated from third party software. The
number of rows and columns of data can vary but at the very least the
report will always start on Row 2 and data will fill at least the
first three columns.

for example:

A B
C D

1

2 Customer Name Route Flag

3 Smith Joe 2 X

4 Collins Tom 1 X

5 Jones Peter 2 X


I am trying to write a macro that would concatenate the two name
fields into a single cell. Because the amount of data can vary I can
not use the concatenate function as the macro has to be intelligent
enough to detect the first empty column.

I have written some code, at least starting with a static column but
have had no luck. Here is the code I have wrote (it has gone through
several iterations so my loop type choices, etc may seem odd).

Sub concatenate()

Dim FirstName As Range
Dim LastName As Range
Dim DestCell As Range

Set FirstName = Worksheets("Sample Report").Range("a3")
Set LastName = Worksheets("Sample Report").Range("b3")
Set DestCell = Worksheets("Sample Report").Range("j3")

Do Until IsEmpty(FirstName.Value)

DestCell.Value = FirstName & ", " & LastName
FirstName.Offset(1, 0).Activate
LastName.Offset(1, 0).Activate
DestCell.Offset(1, 0).Activate

Loop

End Sub

I have tried several things. I either get it to concatenate the first
row and copy that into every single destination cell in the
spreadsheet, get the code stuck toggling between two active cells (I
believe that is how it is now), or any number of compile erriors, etc
if I play with the offset properties.

Any help is greaty appreciated.

Thanks.

-Mike