Concatenated data in subsequent rows

Catalin.B · #1 09-15-2011, 10:37 PM

cell D5 was formatted with data validation to allow only Cities from column B to appear in drop down list... and it was set to give you a message if you enter a city that is not in column B list...

Code:

Sub concatenate()
Dim nextRow As Long, i As Integer
With Sheets("Sheet1")
For i = 5 To 9
nextRow = Range("G" & Rows.Count).End(xlUp).Row + 1
Cells(nextRow, "G").Value = Cells(2, "A").Value & Cells(i, "D").Value
Cells(nextRow + 1, "G").Value = Cells(3, "A").Value & Cells(i, "D").Value
Cells(nextRow + 2, "G").Value = Cells(4, "A").Value & Cells(i, "D").Value
Cells(nextRow + 3, "G").Value = Cells(5, "A").Value & Cells(i, "D").Value
Next i
MsgBox "Mission accomplished! :)"
End With
End Sub

Here is the new code for what you need. Now it is set to take values from range D5 to D9, but you can set your desired number of cities by changing in this line:

Code:

For i = 5 To 9

to

Code:

For i = 5 To 100

... And if you do not want the message box to appear, just delete code line 11

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Odd behaviour of table - keep rows together	Stephan Lindner	Word Tables	1	09-10-2011 05:31 AM
Product and Sum on 2 rows	apolloman	Excel	2	04-27-2011 07:14 AM
Rows to Columns	kyakobi	Excel	5	12-07-2010 03:12 PM
Average of many rows	speedycorn1	Excel	1	10-30-2010 07:54 PM
How do I turn the top 2 rows into a header	GR8Fandini	Excel	1	02-06-2010 12:58 PM