Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# conditional row delete

Posted on 2013-10-23
Medium Priority
200 Views
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
Question by:vbaabv
• 2

LVL 18

Expert Comment

ID: 39595650
Can you provide a sample workbook?
0

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 39595669
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

Author Comment

ID: 39609905
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

Author Closing Comment

ID: 39609906
Thank you very much. Excellent work !
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month6 days, 4 hours left to enroll