5 Ways To Delete Rows Using Vba

Deleting rows in Excel can be a tedious task, especially when working with large datasets. However, with the power of VBA (Visual Basic for Applications), you can automate this process and save time. In this article, we will explore five ways to delete rows using VBA, each with its own unique approach and benefits.

Method 1: Deleting Rows Based on a Specific Condition

This method involves deleting rows based on a specific condition, such as a value in a particular column. For example, let's say you want to delete all rows where the value in column A is "Delete".

Sub DeleteRowsBasedOnCondition()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    For i = lastRow To 1 Step -1
        If ws.Cells(i, "A").Value = "Delete" Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

This code loops through each row in the worksheet, starting from the bottom, and checks if the value in column A is "Delete". If it is, the row is deleted.

Method 2: Deleting Rows Based on a Range

This method involves deleting rows based on a specific range, such as a selection of cells.

Sub DeleteRowsBasedOnRange()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    Dim rng As Range
    Set rng = Selection
    
    rng.EntireRow.Delete
End Sub

This code deletes the entire row of the selected cells.

Method 3: Deleting Rows Using the AutoFilter Method

This method involves using the AutoFilter method to filter the data and then deleting the rows that do not meet the criteria.

Sub DeleteRowsUsingAutoFilter()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ws.Range("A1:A" & lastRow).AutoFilter Field:=1, Criteria1:="<>Delete"
    
    Dim rng As Range
    Set rng = ws.AutoFilter.Range.Offset(1, 0).Resize(ws.AutoFilter.Range.Rows.Count - 1, 1)
    
    rng.EntireRow.Delete
    
    ws.AutoFilterMode = False
End Sub

This code filters the data in column A to show only the rows that do not contain the value "Delete". It then deletes these rows.

Method 4: Deleting Rows Using the Find Method

This method involves using the Find method to find specific values in the data and then deleting the rows that contain these values.

Sub DeleteRowsUsingFind()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim cell As Range
    Set cell = ws.Range("A1:A" & lastRow).Find("Delete")
    
    Do While Not cell Is Nothing
        cell.EntireRow.Delete
        Set cell = ws.Range("A1:A" & lastRow).Find("Delete")
    Loop
End Sub

This code finds all cells in column A that contain the value "Delete" and deletes the rows that contain these cells.

Method 5: Deleting Rows Using the SpecialCells Method

This method involves using the SpecialCells method to find and delete rows that contain formulas or other specific types of data.

Sub DeleteRowsUsingSpecialCells()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    Dim rng As Range
    Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
    
    rng.EntireRow.Delete
End Sub

This code finds all cells in the worksheet that contain formulas and deletes the rows that contain these cells.

Delete Rows Using VBA Image Gallery

We hope this article has provided you with a comprehensive guide on how to delete rows using VBA in Excel. Whether you're looking to delete rows based on a specific condition, range, or method, we've got you covered. Remember to always test your code and adjust it according to your specific needs. Happy coding!

Take Action:

• Try out each of the methods described in this article to see which one works best for your specific use case.
• Experiment with different conditions, ranges, and methods to delete rows in your Excel worksheet.
• Share your experiences and tips with others in the comments below.
• Don't forget to subscribe to our blog for more VBA tutorials and tips!