Remove Blank cells from rows and coluns

[amit_gupta123]

Dear All

The problem is that I have data in appx 2000-2500 rows and 20-30 columns. The data is in text format and then seperated by delimiter "|" (pipe)Few cells remain empty on random basis. I want the output where all the blank cells get removed from the data. The sample input and output files are attached herewith.

Regards

 

[Nebu]

Hi:

Use the following code

Sub test()
Application.ScreenUpdating = False

Dim arr

i& = Me.Cells(Rows.Count, "A").End(xlUp).Row
cnt1& = 3

For j& = 1 To i
    cnt& = 2
    arr = Split(Range("A" & j), "|")
    For k& = LBound(arr) To UBound(arr)
        If arr(k) = vbNullString Then GoTo nextline
            Cells(cnt1, cnt) = arr(k)
            cnt = cnt + 1
nextline:
    Next
    cnt1 = cnt1 + 1
Next

Application.ScreenUpdating = True

End Sub

Thanks

 

[amit_gupta123]

thanx a lot sir