VBA to delete row based on value

July 28, 2010 at 10:49:01
Specs: Windows XP
I am trying to write a macro that will check a value in a column ( say < 100) and based on the answer either keep it or delete the entire row not just the data. I am trying to cull a list of any item less than a hundred in column d. Thank you

See More: VBA to delete row based on value

Report •


#1
July 28, 2010 at 11:25:33
The trick to deleting Rows via VBA is that they must be checked/deleted from the bottom up. If you start at the top and work your way down, a Row will be deleted, which will move all of the other Rows up at the same time that the For-Next counter increments.

In other words, if you delete Row 2 when the For-Next counter is at 2, the original Row 3 will move up to replace Row 2, but the counter will increment to 3 and never check the original Row 3.

So...

Option Explicit
Sub DelLessThan100()
Dim lastRow, rwNum As Integer
'Determine the last Row with data in Column D
 lastRow = Cells(Rows.Count, "D").End(xlUp).Row
'Loop through rows in reverse order
  For rwNum = lastRow To 1 Step -1
'Delete Rows where Column D is <100
   If Cells(rwNum, "D") < 100 Then
    Cells(rwNum, "D").EntireRow.Delete
   End If
  Next
End Sub


Report •
Related Solutions


Ask Question