VBA to Add up and Append the strings in two columns and paste it in one column..How do i do it?

What is wrong with this code?

 Sub Rectangle4_Click()
Dim lrow As Integer
lrow = ThisWorkbook.Sheets("SHEET1").Cells(Rows.Count, 11).End(xlUp).row
Dim row As Integer
Dim srow As Integer
Dim hrow As Integer
srow = 5
row = 5
hrow = 1
Do While Cells(row, 11) <> ""
Cells(row, 13) = Cells(row, 11)
row = row + 1
Loop
Do While Cells(srow, 12) <> ""
Cells(lrow + hrow, 13) = Cells(srow, 12)
srow = srow + 1
hrow = hrow + 1
Loop
End Sub

Help please?

Top Contributor Asked on June 21, 2016 in VBA: Programming.
Add Comment
4 Answer(s)
Best answer

Thank you!!

Top Contributor Answered on June 23, 2016.
Add Comment

Hi Chait,

Welcome to our Forum.

The issues in you code are ,:

    1. ou are not resetting you lrow in the second do whille statement, so its keep on printing in the values.
    2. You have’t  uses any concatenate operation to join the strings.( i.e; line 16 in the above code) 

Please see the below code to concatenate note stings in two columns:

Sub Rectangle4_Click()
Dim StartRow As Integer
Dim LastRow As Integer
LastRow = ThisWorkbook.Sheets("SHEET1").Cells(Rows.Count, 11).End(xlUp).row
StartRow = 5
Do While StartRow <= LastRow
Cells(StartRow, 13) = Cells(StartRow, 11) & " " & Cells(StartRow, 12)
StartRow = StartRow + 1
Loop
End Sub

 

Hope this helps!

Thanks-PNRao!

Keymaster Answered on June 21, 2016.
Add Comment

Thank you for your quick response but iam not looking to concatenate …iam looking it put them in one single column one after the other,,,if a column has names a,b,c,d,e…till some row and another column has w,x,y,z then when i click a button my thrd column should have a,b,c,d,e,w,x,y,z in one single column

Top Contributor Answered on June 22, 2016.
Add Comment

Thanks, now I understand your requirement, after reading your example. Please check the below procedure:

Sub Rectangle4_Click()
'Declaration
Dim iCntr, startRow, lrowCol1, lrowCol2, cntrCol3 As Integer
'Initiate
startRow = 5
cntrCol3 = startRow
'Check the last row in Column 11 and enter them in column 13
lrowCol1 = ThisWorkbook.Sheets("SHEET1").Cells(Rows.Count, 11).End(xlUp).Row
For iCntr = startRow To lrowCol1
If Trim(Cells(iCntr, 11)) <> "" Then
Cells(cntrCol3, 13) = Cells(iCntr, 11)
cntrCol3 = cntrCol3 + 1
End If
Next
'Check the last row in Column 12 and append them in column 13
lrowCol2 = ThisWorkbook.Sheets("SHEET1").Cells(Rows.Count, 12).End(xlUp).Row
For iCntr = startRow To lrowCol2
If Trim(Cells(iCntr, 12)) <> "" Then
Cells(cntrCol3, 13) = Cells(iCntr, 12)
cntrCol3 = cntrCol3 + 1
End If
Next
End Sub 

Thanks-PNRao!

Keymaster Answered on June 22, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.