VBA to Copy paste cell description if there is not data in a particular cell

[zmaniar]

Hello. I am try to figure out a VBA to resolve this issue. I have got the state from the bank but the description is in multiline. I need get everything in one line.

The current sitatuation is

Column A | Column B
Row 1. 23.

11-Jun-21​	UtilityBill Paid 2326****11
	FRM 191*****03
	TO HBL 406******3
	KESC0001 040*******29
	thru Digital Banking

12-Jun-21​	Debit Card/POS 8******4925
	PKR ***9.00 114925 1206 53******
	E********O KARACHI
14-Jun-21​	Debit Card/POS 98101*****218
	PKR 1****.00 00**8 1406 5366**
	B******K KARACHI

What I need is

11-Jun-21​	UtilityBill Paid 2**********80611 FRM 191*********3 TO HBL 406*********03 K*********01 040********* thru Digital Banking
12-Jun-21​	Debit Card/POS 8*********14925 PKR 1*********.00 114925 1206 5*********190 E*********O KA*********CHI

Can anyone help?

 

[Zot]

Try this

Sub Arrange()

Dim n As Long, m As Long
Dim cell As Range, rngData As Range

Set rngData = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Offset(0, -1)

n = 0
For Each cell In rngData
    If Not cell = "" Then
        n = n + 1
        Range("D" & n) = cell.Value
        Range("E" & n) = Range("B" & cell.Row) & " "
    Else
        Range("E" & n) = Range("E" & n).FormulaR1C1 & " " & Range("B" & cell.Row)
    End If
Next

End Sub

 

[zmaniar]

Thank you Zot.

It's replicating Column A & B to D & E. Nothing changed

 

[Zot]

I did not understand what you meant. This is what I got when I ran the code

Book1
	A	B	C	D	E
1	11-Jun-21	UtilityBill Paid 2326****11		11-Jun-21	UtilityBill Paid 2326****11 FRM 191*****03 TO HBL 406******3 KESC0001 040*******29 thru Digital Banking
2		FRM 191*****03		12-Jun-21	Debit Card/POS 8******4925 PKR ***9.00 114925 1206 53****** E********O KARACHI
3		TO HBL 406******3		14-Jun-21	Debit Card/POS 98101*****218 PKR 1****.00 00**8 1406 5366** B******K KARACHI
4		KESC0001 040*******29
5		thru Digital Banking
6	12-Jun-21	Debit Card/POS 8******4925
7		PKR ***9.00 114925 1206 53******
8		E********O KARACHI
9	14-Jun-21	Debit Card/POS 98101*****218
10		PKR 1****.00 00**8 1406 5366**
11		B******K KARACHI
Sheet1

 

[zmaniar]

Strange. If I copy the data from above and run the script it works. But I am running it on the actual data it isn't working.

Any way I can put the excel file here?

 

[zmaniar]

Thank you @Zot. Something is wrong the format. It's working now.

 

[Zot]

Strange. If I copy the data from above and run the script it works. But I am running it on the actual data it isn't working.

Any way I can put the excel file here?

I actually copy paste your data there ?

The tool to select range on sheet capture and paste is XL2BB (the right most icon)

You need to install the Add-In. After paste it looks like rubbish. To view result click Preview. Click it again to go back to writing mode.

Are you sure your column is A and B?