Previous Topic Next Topic

Delete Only The Rows with Numeric Value MACRO

Closed
NewbieinSF - Sep 13, 2010 at 06:12 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 15, 2010 at 09:13 PM
Hello,

I am struggling with trying to figure out a macro to look through a selection of cells that have either numeric or text values, and I need to sift through the entire selection to identify the ones with a number, and if that's the case, I would need to delete the entire row. Basically in a range of

XXXXXX
111111
YYYYYYY
222222
222222
222233
TRES
TEXT

I need to look in the entire column and identify the cells with numbers and delete then, while leaving the rows with text value.

Any help with this will great.

2 responses

Answer 1 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 13, 2010 at 10:34 PM
the data is range A1 down
A1 is column heading
before running the macro copy the data safely in another sheet and save the file


data
XXXXXX
111111
YYYYYYY
222222
222222
222233
TRES
TEXT

they try this macro 

Sub test()
Dim j As Integer, k As Integer, r As Range
j = Cells(Rows.Count, "A").End(xlUp).Row
For k = j To 2 Step -1
Set r = Cells(k, 1)
If IsNumeric(r) Then r.EntireRow.Delete
Next k
0
NewbieinSF
Sep 15, 2010 at 12:04 PM
Thanks so much - I see that your macro is working but I can't change it to look at the date which for me is in column "P"; I tried changing it to j = Cells(Rows.Count, "P").End(xlUp).Row
But this way it doesn't work.
0
Answer 2 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 15, 2010 at 09:13 PM
you have correctly changed the line. one more line has to be changed

Set r = Cells(k, "P")

after changing now you try.
0