Solved

What VBA code (Excel 2010) would I use to delete rows based on specific  numbers in the cells of one column?

Posted on 2013-01-14
10
494 Views
Last Modified: 2013-01-15
I have an Excel 2010 spread sheet that contains multiple columns. Column C consists of cells with numbers that range from 1 - 109. I want to automate the deletion of the rows that contain the following numbers in column C -  2, 4, 6, 8,10, 96, 98, 100,102, 104,106, 108.

What code could I use to accomplish this task?

Thanks
0
Comment
Question by:PDSWSS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38776884
Here's a macro.

Sub RemoveNbrs()
Dim lngLastRow As Long
Dim lngrow As Long

lngLastRow = Range("C65536").End(xlUp).Row

For lngrow = lngLastRow To 1 Step -1
    If Cells(lngrow, 3).Value = "2" Or _
         Cells(lngrow, 3).Value = "4" Or _
        Cells(lngrow, 3).Value = "6" Or _
        Cells(lngrow, 3).Value = "8" Or _
        Cells(lngrow, 3).Value = "10" Or _
        Cells(lngrow, 3).Value = "96" Or _
        Cells(lngrow, 3).Value = "98" Or _
        Cells(lngrow, 3).Value = "100" Or _
        Cells(lngrow, 3).Value = "102" Or _
        Cells(lngrow, 3).Value = "104" Or _
        Cells(lngrow, 3).Value = "106" Or _
        Cells(lngrow, 3).Value = "108" Then
            Rows(lngrow).EntireRow.Delete
    End If
Next

End Sub

Open in new window

0
 

Author Comment

by:PDSWSS
ID: 38776893
Thanks. Will test as soon as I get the chance.
0
 

Author Comment

by:PDSWSS
ID: 38776966
I tested your macro without success.  The sample excel file is attached.

BTW- The column of interest is  "G" not "C" which I changed in your code.

Please test your code and let me know what you find. thanks
CTF2010EE.xlsm
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 48

Accepted Solution

by:
Martin Liss earned 300 total points
ID: 38776982
The syntax for Cells is Cells(row, column)

Sub RemoveNbrs()
Dim lngLastRow As Long
Dim lngrow As Long

Application.ScreenUpdating = False
lngLastRow = Range("G65536").End(xlUp).Row

For lngrow = lngLastRow To 1 Step -1
    If Cells(lngrow, 7).Value = "2" Or _
         Cells(lngrow, 7).Value = "4" Or _
        Cells(lngrow, 7).Value = "6" Or _
        Cells(lngrow, 7).Value = "8" Or _
        Cells(lngrow, 7).Value = "10" Or _
        Cells(lngrow, 7).Value = "96" Or _
        Cells(lngrow, 7).Value = "98" Or _
        Cells(lngrow, 7).Value = "100" Or _
        Cells(lngrow, 7).Value = "102" Or _
        Cells(lngrow, 7).Value = "104" Or _
        Cells(lngrow, 7).Value = "106" Or _
        Cells(lngrow, 7).Value = "108" Then
            Rows(lngrow).EntireRow.Delete
    End If
Next
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38777043
Hi

A non loop method.

Sub kTest()
    
    Dim MyNums, i As Long, r As Range
    
    MyNums = Array(2, 4, 6, 8, 10, 96, 98, 100, 102, 104, 106, 108)
    
    Set r = Range("g1:g" & Range("g" & Rows.Count).End(3).Row)
    
    Const TempString = "##"
    
    Application.ScreenUpdating = 0
    With r
        On Error Resume Next
        .SpecialCells(4) = TempString
        For i = LBound(MyNums) To UBound(MyNums)
            .Replace MyNums(i), vbNullString, 1
        Next
        .SpecialCells(4).EntireRow.Delete
        .Replace TempString, vbNullString, 1
    End With
    Application.ScreenUpdating = 1
    
End Sub

Open in new window


Kris
0
 

Author Comment

by:PDSWSS
ID: 38777084
MartinLiss

Did you test your updated code on the file I attached? Does not run and don't know why.
Other code works.  Thanks
0
 

Author Comment

by:PDSWSS
ID: 38777092
Kris

 Same issue with your code. Did you test your updated code on the file I attached? Does not run and don't know why.
Other code works.  Thanks
0
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 200 total points
ID: 38777156
It works fine for me. BTW, replace Set r = ... with

With Worksheets("CTF2010macro")
        Set r = .Range("g1:g" & .Range("g" & .Rows.Count).End(3).Row)
    End With

Open in new window


Kris
0
 

Author Comment

by:PDSWSS
ID: 38778257
Both solutions work. Don't know what the issue was last night.

Since Martin Liss provided a correct solution first, will give him 300 points and
200 points to Kris.   Thanks for your help.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 38778400
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

623 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