Hello,
I have some sort of problem with clubing the data. I have attached a excel file for better explanation.
Waiting for your reply,
krrish
Hello,
I have some sort of problem with clubing the data. I have attached a excel file for better explanation.
Waiting for your reply,
krrish
Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going
[vba]
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Application.ScreenUpdating = False
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = Lastrow To 2 Step -1
If .Cells(i, "A").Value2 = "" Then
.Cells(i - 1, "B").Value2 = .Cells(i - 1, "B").Value2 & " " & .Cells(i, "B").Value2
.Rows(i).Delete
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
XLDOriginally Posted by xld
Thanks for the reply, But I want the results as in excel file.
The "C" Column is the Results, "A" and "B" are the sources.
For example, I have Data in "A1" and "A5" ["A2,A3,A4" are blanks] and "B1 to B6". So B1&B2 ... B4 should be placed in "C1" then "B5 & B6" Should be placed in "C5" on the basis of value in "A5"
Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going
Any help guys?
Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going
Finally I got it Myself.
Xld your tips on Improving Programming really helps.
Just practice and Practice... []
thanks bro.
here My code:
[VBA]Sub Concatenate()
Dim i, lr As Long
Dim tmp As String
Dim k As Integer
Application.ScreenUpdating = False
lr = ActiveSheet.UsedRange.Rows.Count
For i = 1 To lr
If Range("A" & i).Value <> 0 Then
tmp = Range("B" & i).Value
k = i
Else
tmp = tmp & " " & Range("b" & i).Value
End If
Range("C" & k).Value = tmp
Next
tmp = ""
Application.ScreenUpdating = True
End Sub
[/VBA]
Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going
Took me a while to figure out what you meant, but I think this is it
[vba]
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Application.ScreenUpdating = False
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = Lastrow To 1 Step -1
If .Cells(i, "A").Value2 <> "" Then
If .Cells(i + 1, "A").Value2 <> "" Or i = Lastrow Then
.Cells(i, "C").Value2 = .Cells(i, "B").Value2
Else
.Cells(i, "C").Value2 = .Cells(i, "B").Value2 & " " & .Cells(i + 1, "C").Value2
.Cells(i + 1, "C").Value2 = ""
End If
Else
If .Cells(i + 1, "A").Value2 = "" Or i = Lastrow Then
.Cells(i, "C").Value2 = .Cells(i, "B").Value2 & " " & .Cells(i + 1, "C").Value2
.Cells(i + 1, "C").Value2 = ""
Else
.Cells(i, "C").Value2 = .Cells(i, "B").Value2
End If
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Xld
Thank you very much.
Happiness keeps u sweet, trials keep u strong, sorrow keeps u human, failure keeps u humble, success keeps u glowing, but only God keeps u going