Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge to Columns with Unknown length
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 merge these 2 columns from a helper column I created - the only problem bein I do not know how many House names or numbers need merging pe spreadsheet so my macro automatical self calculates 500 rows but th database these spreadsheets can uploaded in to keeps reading the blan spaces left behind in the empty rows and failing Anyone know how to let the macro know when to stop at the last row o data please Any help appreciate -- Steve ----------------------------------------------------------------------- Steve M's Profile: http://www.excelforum.com/member.php...fo&userid=3252 View this thread: http://www.excelforum.com/showthread.php?threadid=52349 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge to Columns with Unknown length
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge to Columns with Unknown length
Thats Brilliant Cheers Stev -- Steve ----------------------------------------------------------------------- Steve M's Profile: http://www.excelforum.com/member.php...fo&userid=3252 View this thread: http://www.excelforum.com/showthread.php?threadid=52349 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge to Columns with Unknown length
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge to Columns with Unknown length
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" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge to Columns with Unknown length
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a string of unknown length in a string of unknown length, Help! | Excel Discussion (Misc queries) | |||
SMALL() on unknown length of list | Excel Worksheet Functions | |||
How do I sum a column of unknown length using a macro | Excel Programming | |||
How to find out merge cells from unknown position | Excel Discussion (Misc queries) | |||
Sum unknown length column data in VBA | Excel Programming |