Asking for your help with VBA code to delete duplicate numbers and rearrange data, I attached File, 2 columns Day and Time related to each other. Thank you
VBA code to remove duplicate numbers and rearrange data
- HGVIET
- Thread is marked as Resolved.
-
-
Carim
Changed the title of the thread from “Please help me with VBA code to remove duplicate numbers and rearrange data” to “VBA code to remove duplicate numbers and rearrange data”. -
Try the attached, click the button on Sheet1.
Code assigned to the button:
Code
Display MoreSub RemDupsSort() Dim x, i&, ii&, iii&, s$, r As Range x = ActiveSheet.[c2].CurrentRegion With ActiveSheet.[c2].CurrentRegion x = .Value2 For i = 2 To UBound(x) For ii = 1 To UBound(x, 2) - 1 Step 2 s = x(i, ii) & x(i, ii + 1) iii = i + 1 While iii < UBound(x) If x(iii, ii) & x(iii, ii + 1) = s Then x(iii, ii) = vbNullString x(iii, ii + 1) = vbNullString End If iii = iii + 1 Wend Next Next .Value2 = x Application.ScreenUpdating = 0 For i = 1 To UBound(x, 2) - 1 Step 2 Set r = .Columns(i).Resize(, 2) r.Sort .Columns(i + 1), , , , , , , 1 r.Sort .Columns(i), , , , , , , 1 Next End With End Sub
-
Thank you very much, please take a look at it again, run it a second time to get the results
-
-
Not sure what you mean by "run it again to get results", clicking the button a second time changes nothing.
It might help if you included a sheet which shows the result you expect.
-
-
-
To delete duplicate rows and rearrange the data in the way you described, you can use the following VBA code:
Code
Display MoreSub RemoveDuplicatesAndSort() Dim ws As Worksheet Dim lRow As Long Dim i As Long Dim j As Long Dim temp As String Set ws = ThisWorkbook.Sheets("Sheet1") ' change "Sheet1" to your sheet name lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' get the last row in column A ' loop through rows and delete duplicates For i = lRow To 2 Step -1 temp = ws.Cells(i, "A").Value & ws.Cells(i, "B").Value For j = i - 1 To 2 Step -1 If ws.Cells(j, "A").Value & ws.Cells(j, "B").Value = temp Then ws.Rows(j).Delete End If Next j Next i ' sort the data by column A and then column B ws.Range("A2:B" & lRow).Sort Key1:=ws.Range("A2"), Order1:=xlAscending, _ Key2:=ws.Range("B2"), Order2:=xlAscending, Header:=xlNo End Sub
To use this code, copy and paste it into the VBA editor and then run it by pressing the "Run" button or by pressing F5. Make sure to change the sheet name in line 6 to the name of the sheet where your data is located.
I hope this helps! Let me know if you have any questions.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!