• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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

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
PDSWSS
Asked:
PDSWSS
  • 5
  • 3
  • 2
2 Solutions
 
Martin LissOlder than dirtCommented:
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
 
PDSWSSAuthor Commented:
Thanks. Will test as soon as I get the chance.
0
 
PDSWSSAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Martin LissOlder than dirtCommented:
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
 
krishnakrkcCommented:
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
 
PDSWSSAuthor Commented:
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
 
PDSWSSAuthor Commented:
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
 
krishnakrkcCommented:
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
 
PDSWSSAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now