Excel VBA Macro: Delete Rows (Based on Cell Values in Multiple Columns)

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

Пікірлер: 15

  • @ItsTyara
    @ItsTyara5 ай бұрын

    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 ??

  • @sakhilengwenya594
    @sakhilengwenya594Ай бұрын

    Is there a reason why didn’t you do a For loop, this looks complicated

  • @SMJ0192
    @SMJ0192 Жыл бұрын

    nice

  • @greggowaffles

    @greggowaffles

    Жыл бұрын

    Thanks!

  • @josea.pascualr.4779
    @josea.pascualr.4779 Жыл бұрын

    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?

  • @deepk82
    @deepk824 ай бұрын

    Why did we give i,6 or i,10 ? Cant we give just a universal condition for the entire row?

  • @greggowaffles

    @greggowaffles

    4 ай бұрын

    That’s a good point. I’ll make a video on this scenario

  • @SMJ0192
    @SMJ0192 Жыл бұрын

    Can I loop it where the cells check against other cells on another worksheet and remove the rows from the first worksheet?

  • @greggowaffles

    @greggowaffles

    Жыл бұрын

    Yeah. So based on values in a list on another worksheet?

  • @SMJ0192

    @SMJ0192

    Жыл бұрын

    @@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)

  • @greggowaffles

    @greggowaffles

    Жыл бұрын

    @@SMJ0192 cool. I’ll have a video on this by Sunday

  • @SMJ0192

    @SMJ0192

    Жыл бұрын

    @@greggowaffles Awesome, I look forward to it. Thanks for getting back to me!

  • @greggowaffles

    @greggowaffles

    Жыл бұрын

    @@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