macro to concatenate, delete and shift cells

I am trying to write a macro that when a user selects a cell and runs the macro, the following will happen (for example, user select cell b1)
- b1 is selected and the contents of c1 is concatenated to the contents of b1, with a space between the two strings, and the complete string is placed in b1.
- c1 is deleted, and the remaining row is shifted left one position, to make up for the deleted cell.

I also want a variation of this that will do 3 cells:
- b1 is selected and the contents of c1 is concatenated to the contents of b1, with a space between the two strings, and the contents of d1 is then contatenated with b1 and the complete string is placed in b1. e.g. b1 = b1 & " " &c1&" "&d1
- c1and d1 are deleted, and the remaining row is shifted left two positions, to make up for the deleted cells.

All the example I've seen rely on a specified cell (e.g. Range("B1")) and I want to have it operate on the user selected cell.

So far, I've come up with:

Range("A35").Select
ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[2]"
Range("A36").Select

But it does not meet my needs....any help is appreciated
Thanks
John

So far, I've come up with:

Range("A35").Select
ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[2]"
Range("A36").Select

But it does not meet my needs....any help is appreciated

LOL. No i wouldn't think it would. Try this:

Option Explicit

Sub OneCell()
Dim avalue As String, bvalue As String

avalue = ActiveCell.Value
bvalue = ActiveCell.Offset(0, 1).Value

ActiveCell.Value = avalue & " " & bvalue
ActiveCell.Offset(0, 1).ClearContents

End Sub

Sub ThreeCells()
Dim avalue As String, bvalue As String, cvalue As String

avalue = ActiveCell.Value
bvalue = ActiveCell.Offset(0, 1).Value
cvalue = ActiveCell.Offset(0, 2).Value

ActiveCell.Value = avalue & " " & bvalue & " " & cvalue
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents

End Sub

Two macros. One for each scenario you wanted. Like you requested it will work on whatever cell you have selected when you run the macro.

I wasn't sure if you wanted to delete the whole column or just the cell contents so i put ClearContents. You could also put .Delete. I wasn't sure if you wanted a space between the second two values so i put one there anyways. Let me know.

Hi - I sort of have the same request but on larger scale...

I have a workbook with many tabs - Each tab has the first two rows of data that need to be concatenated together into a new row right underneath them and then the upper two rows to be deleted.

I've attached a workbook, what's need is the Row 3 to automate... Any help would be really appreciated, and thank you in advance

Thanks,
Luclarie

Originally Posted by three_jeeps

I am trying to write a macro that when a user selects a cell and runs the macro, the following will happen (for example, user select cell b1)
- b1 is selected and the contents of c1 is concatenated to the contents of b1, with a space between the two strings, and the complete string is placed in b1.
- c1 is deleted, and the remaining row is shifted left one position, to make up for the deleted cell.

I also want a variation of this that will do 3 cells:
- b1 is selected and the contents of c1 is concatenated to the contents of b1, with a space between the two strings, and the contents of d1 is then contatenated with b1 and the complete string is placed in b1. e.g. b1 = b1 & " " &c1&" "&d1
- c1and d1 are deleted, and the remaining row is shifted left two positions, to make up for the deleted cells.

All the example I've seen rely on a specified cell (e.g. Range("B1")) and I want to have it operate on the user selected cell.

So far, I've come up with:

Range("A35").Select
ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[2]"
Range("A36").Select

But it does not meet my needs....any help is appreciated
Thanks
John