PDSWSS
asked on
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
What code could I use to accomplish this task?
Thanks
ASKER
Thanks. Will test as soon as I get the chance.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi
A non loop method.
Kris
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
Kris
ASKER
MartinLiss
Did you test your updated code on the file I attached? Does not run and don't know why.
Other code works. Thanks
Did you test your updated code on the file I attached? Does not run and don't know why.
Other code works. Thanks
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Since Martin Liss provided a correct solution first, will give him 300 points and
200 points to Kris. Thanks for your help.
You're welcome and I'm glad I was able to help.
Marty - MVP 2009 to 2012
Marty - MVP 2009 to 2012
Open in new window