+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Delete rows based on a condition (/showthread.php?tid=5257)
Delete rows based on a condition - Qomplainerz - 07-27-2023
Example:
Sub DeleteRowsBasedOnCondition()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
'Set the worksheet where the data is located
Set ws = ThisWorkbook.Sheets("Sheet1")
'Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'Loop through the rows in reverse order to avoid skipping rows after deletion
For i = lastRow To 2 Step -1
If ws.Cells(i, "A").Value < 10 Then
ws.Rows(i).Delete
End If
Next i
End Sub
Explanation:
The code defines a subroutine named DeleteRowsBasedOnCondition.
It declares variables for the worksheet (ws), the last row with data in column A (lastRow), and a loop counter (i).
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the data is located.
The lastRow variable is determined using ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, which finds the last row with data in column A.
The For loop iterates through the rows in reverse order (from the last row to the second row) to avoid skipping rows after deletion.
The If statement checks the value in column A for each row. If the value is less than 10, the entire row is deleted using ws.Rows(i).Delete.