Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Macro Question

Status
Not open for further replies.

mtpisgah

Technical User
Nov 22, 2006
3
US
Perhaps someone here can point me in the right direction. I've just started trying to write macros in Excel and, umm, have a lot to learn.

I want to create a macro that merges any cell beginning with specific text with the cell directly above. As an example, I have a large Excel spreadsheet that looks similar to this:

1 This is a
2 xxxxsample
3 xxxxsentence.
4 This is also
5 xxxxa sample sentence.
6 This is
7 xxxxalso a
8 xxxxsample sentence.

and so on for several thousand rows, all in one column.

I want the cells beginning with "xxxx" to be merged (concatenated?) with the cell directly above, so that, if this were done for the entire worksheet, each sentence would all be in one cell, as opposed to spread out over multiple rows.

Does this make sense? Is it doable? Any suggestions?

Thanks for any help!
 

You can try this. Test it on a copy of the spreadsheet since it has not error checking etc. Make sure that the cell after your data has nothing in it. To run, make sure that the first row is selected.

Sub Test()
Dim strN As String
Do While Len(ActiveCell.Value) <> 0
'if the value in next row starts with "xxxx",
' copy that value to the end of the current one.
strN = ActiveCell.Offset(1, 0).Value
If Left(strN, 4) = "xxxx" Then
strN = Right(strN, Len(strN) - 4)
ActiveCell.Value = ActiveCell.Value & " " & strN
ActiveCell.Offset(1, 0).EntireRow.Delete
Else 'move to the next row
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

It works by looking at the next row for the "xxxx" key. If the key is found, the text after the key to the end of the current cell and the row is deleted. If the key is not found, the next row becomes the newly selected row. The macro keeps looping until the selected cell is empty.

Again, try this first on a sample, or copy, of your data.

I hope this works for you.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top