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.