• Status: Solved
• Priority: Medium
• Security: Public
• Views: 680

# Excel Selective row delete

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.
0
vbaabv
• 3
• 3
1 Solution

EngineerCommented:
Try this

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 = 7 To nCols Step 12
If IsNumeric(rg.Cells(i, j).Value) And IsNumeric(rg.Cells(i, j + 1).Value) Then
If Abs(rg.Cells(i, j).Value) < 3 And Abs(rg.Cells(i, j + 1).Value) < 3 Then
bDelete = False
Exit For
End If
End If
Next
If bDelete = True Then rg.Rows(i).EntireRow.Delete
Next
End Sub
0

Research ScientistAuthor Commented:
Thank you very much, ssaqibh. However, I can not get it to work. No rows are deleted.

I have attached an Excel file that I tried it out on.

In this file I have colored red the cells that are 3 or less (there will be no negative numbers) in the appropriate columns, and in the first column, I have colored yellow the rows that should be deleted.
EE-test1.xlsm
0

EngineerCommented:
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 = 7 To nCols Step 12
If IsNumeric(rg.Cells(i, j).Value) And IsNumeric(rg.Cells(i, j + 1).Value) Then
If Abs(rg.Cells(i, j).Value) > 3 And Abs(rg.Cells(i, j + 1).Value) > 3 Then
bDelete = False
Exit For
End If
End If
Next
If bDelete = True Then rg.Rows(i).EntireRow.Delete
Next
End Sub
0

Research ScientistAuthor Commented:
Thank you again, ssaqibh.  This works, but deletes too many rows. Let me explain: this script leaves only those rows in which the value is above 3 in all columns checked. However, I want also to keep rows in which some of the checked columns can be 3 or less. It is only rows in which all columns are 3 or less that I want deleted. Your script deletes rows in which there is a mix of 3 or less and above 3, but I want to keep those rows.
0

EngineerCommented:
I think this works now. It does leave out one of the rows which was colored yellow but probably that was wrongly colored.
``````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 = 7 To nCols Step 12
If IsNumeric(rg.Cells(i, j).Value) And IsNumeric(rg.Cells(i, j + 1).Value) Then
If Abs(rg.Cells(i, j).Value) >= 3 Or Abs(rg.Cells(i, j + 1).Value) >= 3 Then
bDelete = False
Exit For
End If
End If
Next
If bDelete Then rg.Rows(i).EntireRow.Select: rg.Rows(i).EntireRow.Delete
Next
End Sub
``````
0

Research ScientistAuthor Commented:
Excellent, ssaqibh !

It is working fine now. And that was a mistake by me, so everything worked fine in the example sheet as well.

Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.