Solved

Excel Selective row delete

Posted on 2013-11-12
6
612 Views
Last Modified: 2013-11-14
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
Comment
Question by:vbaabv
  • 3
  • 3
6 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39643127
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
 

Author Comment

by:vbaabv
ID: 39643313
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39643773
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:vbaabv
ID: 39646001
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 39647589
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

Open in new window

0
 

Author Closing Comment

by:vbaabv
ID: 39649560
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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question