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

# conditional row delete

I have a worksheet with X number of columns (this can run up to 200 columns or more), but the exact number is variable. So, first have to calculate number of rows that happen to be in this particular worksheet.

Then, I want to go through each row and check the value initially in the the ninth column (I), then every 12 columns (U, AG...) thereafter to the end. The number of the last column having been determined in step 1 above. However, the end column will not be the last column checked. The last column will be about 4 columns after the last column checked. For example, because we are checking every 12 columns after column I, the last checked column ( I know this for certain ) will not be the last column in the worksheet, but will be about 4 columns before the end.

For a given row, if all values in all columns that were checked were below 2 and less than -2 (between 2 and -2), then delete the row.
0
vbaabv
• 2
1 Solution

PresidentCommented:
Can you provide a sample workbook?
0

Commented:
Here is a macro that will check your data and delete rows if every twelfth value has an absolute value equal to or less than 2, starting with cell I2.
``````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
``````
AbsoluteValueDeleteQ28275433.xlsm
0

Research ScientistAuthor Commented:
Thank you very much, byundt. Checking this script requires a somewhat complex set of data, so sorry for the delay in getting back to you. This is working very well. Thank you very much for your help.
0

Research ScientistAuthor Commented:
Thank you very much. Excellent work !
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.

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.