vbaabv
asked on
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.Delet e
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.
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
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.Delet
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.
ASKER
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
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
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.Delet e
Next
End Sub
Dim rg As Range
Dim i As Long, j As Long, nCols As Long
Dim bDelete As Boolean
Application.ScreenUpdating
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.Delet
Next
End Sub
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Sub Absolution()
Dim rg As Range
Dim i As Long, j As Long, nCols As Long
Dim bDelete As Boolean
Application.ScreenUpdating
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.Delet
Next
End Sub