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

Question

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

vcoolio · Answer

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.

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

1 response

Similar discussions

Subscribe To Our Newsletter!