Data Reshuffling of Cells [Solved/Closed]

Report
-
 Aslam -
Hello,

Dear Friends,

I need your help to solve the following problem.

I have few files containing data of more than 50,000 records. Some records columns data not according to the headings. Each file containing thousands of records which require column adjustment according to the headings. I have tried it by macro recording but its work only on the row # which recorded in the macro. So, its useless for me.

I need a macro which work only on row if that's row's column Q or R or S or T or U (any of them contain data, use `or' condition) containing data either numeric or text. If this condition get true then the macro do following:

Date in column `E' cut and paste on column `F'
Date in column `J' cut and paste on column `H'
Date in column `L' cut and paste on column `J' then copy `J' and paste it on `G'
Date in column `M' cut and paste on column `W'
Date in column `N' cut and paste on column `X'
Date in column `O' cut and paste on column `K'
Date in column `P' cut and paste on column `L'
Date in column `Q' cut and paste on column `O'
Date in column `R' cut and paste on column `N'
Date in column `U' cut and paste on column `P'

This macro should start work from raw # 5 and ends where the records end.

I will be very grateful to you for this help.

Thank you,

Aslam



1 reply

Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
466
Hi Aslam,

Try this code:
Sub test()
Dim lRow As Integer
'Change Q to column letter with data in the last row.
lRow = Range("Q" & Rows.Count).End(xlUp).Row

For Each cell In Range("Q5:Q" & lRow)
If cell.Value <> "" _
Or cell.Offset(0, 1).Value <> "" _
Or cell.Offset(0, 2).Value <> "" _
Or cell.Offset(0, 3).Value <> "" _
Or cell.Offset(0, 4).Value <> "" Then

cell.Offset(0, -12).Cut Destination:=cell.Offset(0, -11)
cell.Offset(0, -7).Cut Destination:=cell.Offset(0, -9)
cell.Offset(0, -5).Cut Destination:=cell.Offset(0, -7)
cell.Offset(0, -7).Copy Destination:=cell.Offset(0, -10)
cell.Offset(0, -4).Cut Destination:=cell.Offset(0, 6)
cell.Offset(0, -3).Cut Destination:=cell.Offset(0, 7)
cell.Offset(0, -2).Cut Destination:=cell.Offset(0, -6)
cell.Offset(0, -1).Cut Destination:=cell.Offset(0, -5)
cell.Copy Destination:=cell.Offset(0, -2)
cell.Offset(0, 1).Cut Destination:=cell.Offset(0, -3)
cell.Offset(0, 4).Cut Destination:=cell.Offset(0, -1)
cell.ClearContents

End If
Next
End Sub


Best regards,
Trowa
Dear TrowaD,

Thank you very much for your time and attention which you extended to help me.

I am sorry to let you know that this macro is not working as per requirement. Its reshuffle the data but not as per desire.

could you please let me know what no. you assigned to represent the column, if you will let me know this may be I will change data reshuffling according to my requirement.

Or please check this macro once again after study the task once again.

I really appreciate your efforts and I am very much confident that you will do it.

Best regards,

Aslam
Dear TrowaD,

I have solved the problem and now its working according to my requirement. You set Q as 0 while I changed it and set U as 0 then adjusted all the columns and its working good.

Thank you very much for your support and help.

Best regards,

Aslam

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!