Moving the data into previous cell by using excel formula or VBA code

Report
Posts
1
Registration date
Saturday May 1, 2021
Status
Member
Last seen
May 1, 2021
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
hi all,
I am working as Master data analyst. While working with data I got a problem, I have almost 10,000-row lines each having multiple columns. Each row having data and column too. But my problem is that if suppose A1,B1 having data but C1 and D1 data is not available where as in the same row again E1,F1,I1 and J1 having data and G1 & H1 doesn't have any data.So, Those which are having blank cells should be filled by moving the data, If i have 10 line items i can move manually but having more number of line in different rows and columns is difficult to move. If anyone have the solution with EXCEL or VBA code please suggest me

Example format
A1 B1 C1 D1 E1 F1 G1 H1 I1 JI
Rana vim sara kum yash suma
sushil sama suku teja baba


After moving it should become as
A1 B1 C1 D1 E1 F1
Rana vim sara kum yash suma
sushil sama suku teja baba

1 reply

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
229
Hello svtranjit,

The following code may help:-

Option Explicit
Sub Test()

        Dim lr As Long: lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
        Dim lCol As Long: lCol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
        Dim i As Long

Application.ScreenUpdating = False

        On Error Resume Next
                For i = 2 To lr
                     Sheet1.Range(Cells(i, 1), Cells(i, lCol)).SpecialCells(2, 2).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
                Next i
        On Error GoTo 0

Application.ScreenUpdating = True
Sheet2.Select

End Sub


The code assumes that your data is in Sheet1 and starts in row2 with headings in row1. The "repaired" data is transferred to sheet2.


I've attached a sample workbook to show you how this code works. Click on the "TEST ME" button to see how it works.
The code only takes a second to work in the sample but be prepared for it to take up to three minutes to work on about 10,000 rows of data. The time taken may vary from machine to machine.

Here is the link to the sample file:-

https://wetransfer.com/downloads/6849f6666833f01f3ed167765d00487720210507074745/6bc920

I hope that this helps.

Cheerio,
vcoolio.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!