Solved

# Excel Selective row delete

Posted on 2013-11-12

I have an Excel worksheet with up to 200 or more columns, the exact number is variable.

I want to go through row by row and check first columns G and H (7 and 8), then every 12 columns after (so the next two columns would be S and T (19 and 20). What I am checking is if the value of both columns is 3 or below. If all the checked columns are 3 or less, then delete that row.

With the expert help of byundt, who helped on a very similar problem, I can do nearly close to what I want if checking only 1 row at a time. For example with much gratitude to byundt I have :

Sub Absolution()

Dim rg As Range

Dim i As Long, j As Long, nCols As Long

Dim bDelete As Boolean

Application.ScreenUpdating = False

Set rg = ActiveSheet.UsedRange

nCols = rg.Columns.Count

For i = rg.Rows.Count To 2 Step -1 'The first row is header labels. Don't check it for absolute values

bDelete = True

For j = 9 To nCols Step 12

If IsNumeric(rg.Cells(i, j).Value) Then

If Abs(rg.Cells(i, j).Value) > 2 Then

bDelete = False

Exit For

End If

End If

Next

If bDelete = True Then rg.Rows(i).EntireRow.Delete

Next

End Sub

This will check for row 9 and then every 12 (check if the value is > 2 or < -2). However, now I need to check rows 7 and 8, then 19 and 20, 31 and 32, 43 and 44, etc., so I just can not step 12. (I also want to check if values in these columns are 3 or below.) I think the above script by byundt is pretty much the answer, so the question is really how do I check two adjacent columns instead of just one as in the above ? I can not just step 12 because 2 adjacent columns need to be checked.