simple excel VBA programming
 

I am fairly new to Excel and VBA, I have some VBA
background in Access.

I imported some text from a document to paste into an
Excel worksheet, and that went pretty well.

But, some of the columns are askew because the country
column is normally one word, but when a country has two
or three words, then the columns get screwed up. ie.
Canada is in one column, but United States gets put into
two columns.

So now, trying to clean up the columns, it is a real pain
to clean them up. I can't cut and paste the text from one
column into the other, so I thought the concantenate
function might work.

I thought a VBA solution where I highlighted to range of
cells that I want to concantenate, pressed a command
button and voila, the strings would be in one column.

Any other better ideas or thoughts on how to program this?

Paul

simple excel VBA programming
 

Paul,

Here's some code to do it

Dim cCols As Long, j As Long
Dim cRows As Long, i As Long
Dim oStart As Range

Set oStart = Selection.Cells(1, 1)
On Error Resume Next
With Selection
cCols = .Columns.Count
cRows = .Rows.Count
For i = 1 To cRows
For j = 2 To cCols
oStart.Cells(i, 1) = oStart.Cells(i, 1) & " " &
oStart.Cells(i, j)
oStart.Cells(i, j) = ""
Next j
oStart.Cells(i, 1) = Trim(oStart.Cells(i, 1))
Next i
End With


You could do it with worksheet functions, but this is probably simpler.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Paul" wrote in message
...
I am fairly new to Excel and VBA, I have some VBA
background in Access.

I imported some text from a document to paste into an
Excel worksheet, and that went pretty well.

But, some of the columns are askew because the country
column is normally one word, but when a country has two
or three words, then the columns get screwed up. ie.
Canada is in one column, but United States gets put into
two columns.

So now, trying to clean up the columns, it is a real pain
to clean them up. I can't cut and paste the text from one
column into the other, so I thought the concantenate
function might work.

I thought a VBA solution where I highlighted to range of
cells that I want to concantenate, pressed a command
button and voila, the strings would be in one column.

Any other better ideas or thoughts on how to program this?

Paul

simple excel VBA programming
 

Bob


Thanks for the help....I'll try this out later tonight...

Merry Christmas

Paul


-----Original Message-----
Paul,

Here's some code to do it

Dim cCols As Long, j As Long
Dim cRows As Long, i As Long
Dim oStart As Range

Set oStart = Selection.Cells(1, 1)
On Error Resume Next
With Selection
cCols = .Columns.Count
cRows = .Rows.Count
For i = 1 To cRows
For j = 2 To cCols
oStart.Cells(i, 1) = oStart.Cells(i, 1)
& " " &
oStart.Cells(i, j)
oStart.Cells(i, j) = ""
Next j
oStart.Cells(i, 1) = Trim(oStart.Cells(i, 1))
Next i
End With


You could do it with worksheet functions, but this is
probably simpler.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Paul" wrote in
message
...
I am fairly new to Excel and VBA, I have some VBA
background in Access.

I imported some text from a document to paste into an
Excel worksheet, and that went pretty well.

But, some of the columns are askew because the country
column is normally one word, but when a country has two
or three words, then the columns get screwed up. ie.
Canada is in one column, but United States gets put
into
two columns.

So now, trying to clean up the columns, it is a real
pain
to clean them up. I can't cut and paste the text from
one
column into the other, so I thought the concantenate
function might work.

I thought a VBA solution where I highlighted to range
of
cells that I want to concantenate, pressed a command
button and voila, the strings would be in one column.

Any other better ideas or thoughts on how to program
this?

Paul


.