Merge to Columns with Unknown length

[Steve M]

Hi - I have a spreadsheet with 2 columns

House Name
House Number

I want to merge these to columns and I have used the following

=B2&" "&C2&" "

This works fine except I want to run a macro that automatically merges these 2 columns from a helper column I created - the only problem being I do not know how many House names or numbers need merging per spreadsheet so my macro automatical self calculates 500 rows but the database these spreadsheets can uploaded in to keeps reading the blank spaces left behind in the empty rows and failing

Anyone know how to let the macro know when to stop at the last row of data please

Any help appreciated

[Nigel]

Puts the merged cells B and C into cell A for only rows up to the last
column B entry.....

with activesheet
xlastrow = .cells(rows.count,2).end(xlup).row
for x = 1 to xlastrow
.cells(x,1) = .cells(x,2) & " " & .cells(x,3) & " "
next x
end with

--
Cheers
Nigel



"Steve M" <[email protected]> wrote in
message news:[email protected]...
>
> Hi - I have a spreadsheet with 2 columns
>
> House Name
> House Number
>
> I want to merge these to columns and I have used the following
>
> =B2&" "&C2&" "
>
> This works fine except I want to run a macro that automatically merges
> these 2 columns from a helper column I created - the only problem being
> I do not know how many House names or numbers need merging per
> spreadsheet so my macro automatical self calculates 500 rows but the
> database these spreadsheets can uploaded in to keeps reading the blank
> spaces left behind in the empty rows and failing
>
> Anyone know how to let the macro know when to stop at the last row of
> data please
>
> Any help appreciated
>
>
> --
> Steve M
> ------------------------------------------------------------------------
> Steve M's Profile:
http://www.excelforum.com/member.php...o&userid=32520
> View this thread: http://www.excelforum.com/showthread...hreadid=523495
>

[Steve M]

Thats Brilliant

Cheers
Steve

[SITCFanTN]

HI Nigel,

I have the same situation but I need to put the data that is in column
J and move it to column F in the same row. How would I alter this code to
accomplish this. The cells in Column F are empty on these rows where there
is data in column J. Thanks
"Nigel" wrote:

> Puts the merged cells B and C into cell A for only rows up to the last
> column B entry.....
>
> with activesheet
> xlastrow = .cells(rows.count,2).end(xlup).row
> for x = 1 to xlastrow
> .cells(x,1) = .cells(x,2) & " " & .cells(x,3) & " "
> next x
> end with
>
> --
> Cheers
> Nigel
>
>
>
> "Steve M" <[email protected]> wrote in
> message news:[email protected]...
> >
> > Hi - I have a spreadsheet with 2 columns
> >
> > House Name
> > House Number
> >
> > I want to merge these to columns and I have used the following
> >
> > =B2&" "&C2&" "
> >
> > This works fine except I want to run a macro that automatically merges
> > these 2 columns from a helper column I created - the only problem being
> > I do not know how many House names or numbers need merging per
> > spreadsheet so my macro automatical self calculates 500 rows but the
> > database these spreadsheets can uploaded in to keeps reading the blank
> > spaces left behind in the empty rows and failing
> >
> > Anyone know how to let the macro know when to stop at the last row of
> > data please
> >
> > Any help appreciated
> >
> >
> > --
> > Steve M
> > ------------------------------------------------------------------------
> > Steve M's Profile:
> http://www.excelforum.com/member.php...o&userid=32520
> > View this thread: http://www.excelforum.com/showthread...hreadid=523495
> >
>
>
>

[Tom Ogilvy]

In Column J, assuming the values are constants and not formulas and the
other cells are empty:

set rng = Columns(10).SpecialCells(xlConstants)
for each cell in rng
cells(cell.row,"F").Value = cell.Value
Next
rng.ClearContents

--
Regards,
Tom Ogilvy

"SITCFanTN" <[email protected]> wrote in message
news:[email protected]...
> HI Nigel,
>
> I have the same situation but I need to put the data that is in column
> J and move it to column F in the same row. How would I alter this code to
> accomplish this. The cells in Column F are empty on these rows where
there
> is data in column J. Thanks
> "Nigel" wrote:
>
> > Puts the merged cells B and C into cell A for only rows up to the last
> > column B entry.....
> >
> > with activesheet
> > xlastrow = .cells(rows.count,2).end(xlup).row
> > for x = 1 to xlastrow
> > .cells(x,1) = .cells(x,2) & " " & .cells(x,3) & " "
> > next x
> > end with
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "Steve M" <[email protected]> wrote
in
> > message news:[email protected]...
> > >
> > > Hi - I have a spreadsheet with 2 columns
> > >
> > > House Name
> > > House Number
> > >
> > > I want to merge these to columns and I have used the following
> > >
> > > =B2&" "&C2&" "
> > >
> > > This works fine except I want to run a macro that automatically merges
> > > these 2 columns from a helper column I created - the only problem
being
> > > I do not know how many House names or numbers need merging per
> > > spreadsheet so my macro automatical self calculates 500 rows but the
> > > database these spreadsheets can uploaded in to keeps reading the blank
> > > spaces left behind in the empty rows and failing
> > >
> > > Anyone know how to let the macro know when to stop at the last row of
> > > data please
> > >
> > > Any help appreciated
> > >
> > >
> > > --
> > > Steve M
> >
> ------------------------------------------------------------------------
> > > Steve M's Profile:
> > http://www.excelforum.com/member.php...o&userid=32520
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=523495
> > >
> >
> >
> >

[JOUIOUI]

Wow, this works great Tom and I understand the code which is good since I'm
just a beginner at this. Let me take this one step further.

Same scenario with a report that varies in length each day. When I'm
downloading this report, some data is incorrectly placed into Column I, that
should be in Column J on the same row. The cells in those rows in column J
are empty. Some of the information in column I is correct however so I only
want to move the data in each cell in column I that have greater than 7
characters. Is that even doable based on that criteria of greater than 7
characters?


"Tom Ogilvy" wrote:

> In Column J, assuming the values are constants and not formulas and the
> other cells are empty:
>
> set rng = Columns(10).SpecialCells(xlConstants)
> for each cell in rng
> cells(cell.row,"F").Value = cell.Value
> Next
> rng.ClearContents
>
> --
> Regards,
> Tom Ogilvy
>
> "SITCFanTN" <[email protected]> wrote in message
> news:[email protected]...
> > HI Nigel,
> >
> > I have the same situation but I need to put the data that is in column
> > J and move it to column F in the same row. How would I alter this code to
> > accomplish this. The cells in Column F are empty on these rows where
> there
> > is data in column J. Thanks
> > "Nigel" wrote:
> >
> > > Puts the merged cells B and C into cell A for only rows up to the last
> > > column B entry.....
> > >
> > > with activesheet
> > > xlastrow = .cells(rows.count,2).end(xlup).row
> > > for x = 1 to xlastrow
> > > .cells(x,1) = .cells(x,2) & " " & .cells(x,3) & " "
> > > next x
> > > end with
> > >
> > > --
> > > Cheers
> > > Nigel
> > >
> > >
> > >
> > > "Steve M" <[email protected]> wrote
> in
> > > message news:[email protected]...
> > > >
> > > > Hi - I have a spreadsheet with 2 columns
> > > >
> > > > House Name
> > > > House Number
> > > >
> > > > I want to merge these to columns and I have used the following
> > > >
> > > > =B2&" "&C2&" "
> > > >
> > > > This works fine except I want to run a macro that automatically merges
> > > > these 2 columns from a helper column I created - the only problem
> being
> > > > I do not know how many House names or numbers need merging per
> > > > spreadsheet so my macro automatical self calculates 500 rows but the
> > > > database these spreadsheets can uploaded in to keeps reading the blank
> > > > spaces left behind in the empty rows and failing
> > > >
> > > > Anyone know how to let the macro know when to stop at the last row of
> > > > data please
> > > >
> > > > Any help appreciated
> > > >
> > > >
> > > > --
> > > > Steve M
> > >
> > ------------------------------------------------------------------------
> > > > Steve M's Profile:
> > > http://www.excelforum.com/member.php...o&userid=32520
> > > > View this thread:
> http://www.excelforum.com/showthread...hreadid=523495
> > > >
> > >
> > >
> > >
>
>
>