I've come up with a sample file to make it clear what I'm trying to solve and attached it to this reply - the result I want from the macro is in Sheet3, and the data is in Sheet1 and Sheet2.
Posts by pijo
Bumping this in case someone can help me out
Sorry, pretty important typo in my first message. Wherever it says "Column Z" for Sheet2, it should be "Column B" instead. So all data in Column I in Sheet1 should compared against all data in Column B in Sheet2., NOT Column Z. But all the other information and the logic remain the same. You can see I am trying to reference Column B in my code by using an index of 2 for the Sheet2 variant. I tried to edit the post but don't see an option anywhere.
It's clear that the problem originates from the way I'm writing the For loop statements using the variants, but I'm not sure what to change. Still pretty newbie when it comes to VBA syntax.
You are a wizard - this does exactly what I wanted. Thank you!!!
This is awesome, thank you so much! Hope you will not mind if I take this one step further
Let's say I've added a new sheet (Sheet3), and I've also added a new column highlighted in red (H) in Sheet2 with a long list of unique IDs. These IDs relate to the ones recently generated in column A. This new column H will be much longer than the list you helped me generate with the sub you wrote above since it will contain the list of all available IDs and not just the sample ones I provided.
I want to filter even more on the new list just created. I want to check column A against column H for matches. If there is a match, do nothing. If there is NO match (i.e., no ID in column A matches any ID in column H), then I want those "no match" rows (highlighted in blue) to create a list in Sheet3.
I've attached a new sample sheet here: SampleData3.xlsx
I've also tried to edit your code to fit this second problem to be solved. The problem is that, in this case, I want to check A2 against all values from column H (not just H2), A3 against all values from column H (not just H3), and so on. How can I edit this code to fit the scenario?Code
Sub CreateFinalList() Application.ScreenUpdating = False Dim srcWS As Worksheet, desWS As Worksheet, i As Long, v1 As Variant, LastRow As Long Set srcWS = Sheets("Sheet2") Set desWS = Sheets("Sheet3") v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 9).Value For i = 1 To UBound(v1, 1) If v1(i, 1) = v1(i, 8) Then LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 With desWS .Range("A" & LastRow) = v1(i, 1) .Range("D" & LastRow) = v1(i, 4) .Range("E" & LastRow) = v1(i, 5) .Range("F" & LastRow) = v1(i, 6) End With End If Next i Application.ScreenUpdating = True End Sub
For the purposes of the problem I'm trying to solve, I've attached a file with sample data showing exactly what I want to do: SampleData3.xlsx
Please let me know if I can clarify anything I outline - it's a bit difficult to explain.
In Sheet1 of this workbook, I have 4 columns of particular importance: UI1, UI2, UI3, and UI4 (columns C, E, I, and J, respectively). UI stands for "unique identifier". Column UI1 coincides with UI3, and UI2 coincides with UI4. I am only concerned with rows for which either UI1 or UI2 are different from UI3 and UI4. In other words, I want to consider rows where the values in columns C or E are different from the values in columns I or J.
For example, in row 3 of this sample data, columns UI1 and UI3 are the same, AND UI2 and UI4 are the same, therefore there is an exact match in this row and I do not care about it.
However, rows 4 and 5 fit my criteria: in row 4, only UI2 and UI4 are different, and in row 5, both corresponding pairs (UI1/UI3 and UI2/UI4) are different. In either case, I want to write a macro that finds these differences and creates a list exactly like you see in Sheet2 of the workbook.
So, when the macro finishes running on this sample data in Sheet1, we should see 2 rows in this new sheet (Sheet2).
Column A will contain the concatenation of UI3 and UI4 (i.e., for row 4, UI3 = 0071-135, and UI4 = 1, so cell A2 in Sheet2 should read "0071-135-1"). Another example: "0071-130" and "1" should concatenate and become "0070-130-1". Notice that a hyphen is to be added between the concatenated strings, so it's not a "strict" concatenation.
Column D in Sheet2 will be the value from Column K of Sheet1, Column E in Sheet2 will be the value from Column F of Sheet1, and Column F of Sheet2 will be the value of Column G from Sheet1. Everything highlighted in yellow in my sample data is information that should be populated with the macro.
This is amazing!! Thank you Carim
May be the easiest for you is to attach a small file with 5 to 10 records ...BUT with the exact same structure as your working file ...
You're right - sorry about that. I just attached a file with the exact same structure as my working file called Sample Data 2: SampleData2.xlsx
I'd also like to automatically delete rows where the data in the Interface column (column B) does not begin with "Gi". For example, rows 2 and 3 would be removed since the value for Interface in B2 and B3 is "VI2112". And I'd like to remove that "total" row at the bottom of the list (where it says 229 of 229 displayed).
That last bit is just for convenience, to have everything in 1 macro if possible not needed though, can just delete that last row manually.
Attached is your test file
You can run the macro called ' pijoDelete '
Hope this will help
Thanks for this! What if the data is in columns D through AC instead of A through Z like it is in my sample document?
Yes - what I am saying is, if the entire row shows zeroes, then it should be deleted. Otherwise, the row should not be deleted.
For example, in this SampleData file, I would want to have row 101 removed, but NOT row 100
Hello and Welcome to the Forum
The most efficient solution is to use AutoFilter
Attaching a sample would make things a lot easier ...
Attached a file with sample data I would want to filter out/delete
I have a VBA problem - I want to do exactly what was being solved in this thread:
Delete Entire Row if Cells is Zero in Column J VBA
However instead of deleting the entire row based on ONE column being zero (in this example, J), I want to delete entire rows which contains zeroes in all cells in a specified range (columns D through AC). For example, if D2 thru AC2 all contain zeroes, then row 2 should be deleted from the sheet. Also, let's say D2 thru Y2 contain zeroes, but Z2 thru AC2 contain numeric values other than 0. In this case, I would NOT want to delete row 2 from the sheet.
Please let me know how I can change the macro to specify an entire range of columns instead of just one column.
This is the code I took from the above thread (right now it removes all rows where there is a zero in the 10th column):Code
Sub Maybe3() Dim x, i As Long Application.ScreenUpdating = 0 With Sheets("Sheet2").Cells(1).CurrentRegion.Columns(10) '<----- Change to actual sheet name x = .Value For i = 1 To UBound(x, 1) If x(i, 1) = 0 Then x(i, 1) = vbNullString Next .Value = x .SpecialCells(4).EntireRow.Delete End With End Sub
I'm new to this website so don't know how to reach out. If you can message me please do. Thank you!