Delete Rows (Based on Cell Values in Multiple Columns) | Excel VBA Macro
HTML-код
- Опубликовано: 15 окт 2024
- Excel VBA Macro: Delete Rows (Based on Cell Values in Multiple Columns)
💥Subscribe: / @greggowaffles
Code:
Sub delete_rows_based_on_multi_col()
Dim row_count As Long
Dim i As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Countries")
ws.Activate
row_count = ws.Cells(Rows.count, "A").End(xlUp).Row
i = 2
Do While i <= row_count
If Cells(i, 6) = "" _
Or Cells(i, 10) = "" _
Or Cells(i, 5) > 10000 Then
Rows(i).EntireRow.Delete
i = i - 1
row_count = row_count - 1
End If
i = i + 1
Loop
End Sub
#excelmacro #excelvba
You are amazing!!!!!! Thank you!!
Quick Question. I want to delete duplicates. What code do I type to delete duplicates?
If Cells(i,1).Value > 1 ??
If it is possible to delete row if the numbers are between two numbers? Please advise. Thank you.
Is there a reason why didn’t you do a For loop, this looks complicated
I want to do something similar to this...
I have some records and I there's a column that may contain duplicate values and I want that based on a value that is on another column delete one specific row from my duplicates, meaning I just want to keep one specific row based on the values that column F (duplicate values) and column B (what determinates which row I have to delete). For example I have:
Row 1, Column B value = 0, Column F value = 123456
Row 2 ,Column B value = 1, Column F value = 123456
Based on that example I want to delete rows where my Column F value is duplicate and the column B value is 1. In that case Row 2 must be deleted and Row 1 needs to stay.
Is there any way of doing this using VBA?
Why did we give i,6 or i,10 ? Cant we give just a universal condition for the entire row?
That’s a good point. I’ll make a video on this scenario
Can I loop it where the cells check against other cells on another worksheet and remove the rows from the first worksheet?
Yeah. So based on values in a list on another worksheet?
@@greggowaffles yes essentially i have two worksheets and one contains a list of discontinued items with a code. I want to remove the items on sheet1 that have a code in sheet2 (if the discontinued code matches)
@@SMJ0192 cool. I’ll have a video on this by Sunday
@@greggowaffles Awesome, I look forward to it. Thanks for getting back to me!
@@SMJ0192 hey sorry i havent dropped the video yet but heres the code:
Sub delete_rows_based_on_list()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim row_count1 As Long
Dim row_count2 As Long
Dim i As Long
Dim j As Long
Set ws1 = ThisWorkbook.Sheets("sheet1")
Set ws2 = ThisWorkbook.Sheets("sheet2")
ws2.Activate
row_count2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Activate
row_count1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For j = 1 To row_count2
i = 2
Do While i
nice
Thanks!