• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • 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 LissRetired ProgrammerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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