How To Delete Rows In Excel Using Vba

How to Delete Rows in Excel Using VBA

Deleting rows in Excel using VBA is a common task when working with large datasets. VBA provides a simple way to automate this process. Below is an example of how you can delete rows in Excel using VBA:


Sub deleteRows()
  Dim ws As Worksheet
  Dim lastRow As Long
  Dim i As Long
  
  ' Set the worksheet where you want to delete rows
  Set ws = ThisWorkbook.Worksheets("Sheet1")
  
  ' Get the last row in the worksheet
  lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  
  ' Loop through each row in reverse order
  For i = lastRow To 1 Step -1
    ' Check a condition and delete the row if it meets the condition
    If ws.Cells(i, "A").Value = "Delete" Then
      ws.Rows(i).Delete
    End If
  Next i
End Sub
    

In the code above, we first declare the variables that we will use. “ws” is the worksheet object, “lastRow” is a long variable to store the last row number, and “i” is used as a looping variable.

We then set the worksheet where we want to delete the rows. In this example, we are using “Sheet1”. You can change it to the desired worksheet name.

The next step is to get the last row in the worksheet. We use the “End(xlUp)” function to find the last non-empty row in column A.

After that, we loop through each row in reverse order starting from the last row and going up to the first row. This is done to avoid any issues with row numbers changing after deletion.

Inside the loop, we check a condition using an “If” statement. In this example, we check if the value in column A of the current row is equal to “Delete”. If the condition is met, we delete the entire row using the “Delete” method.

Finally, we end the loop and the VBA code.

You can customize the condition and the worksheet name in the code to suit your specific requirements. This is just a basic example to demonstrate the process of deleting rows in Excel using VBA.

Leave a comment