Delete Entire Row in Excel if specific column does not contain a 7-digit number
 

Dear Excel Gurus
I would appreciate your help in composing a macro command that will do
the following:
Imagine that I have a worksheet that contains in column "C" various
7-digit numbers such as for example "2314566"(but in a text format).
Apart from these 7-digit numbers column C contains various other
strings ranging from empty cells to words or a mixture of words and
numbers such as for example "word123" type of strings.
What I would like the macro to do, is to read through the worksheet
(of variable data range each time) and delete entire rows that do not
have in column "C" a pure 7-digit number.Therefore at the end I will
be left with a clean worksheet that will contain only rows that have
7-digit numbers at column C.

Your prompt repsonse will be highly appreciated

Delete Entire Row in Excel if specific column does not contain a 7-digit number
 

Sub test()
Dim rng As Range, bln As Boolean

For Each rng In Range(ActiveSheet.Cells(1, 3),
ActiveSheet.Cells(Rows.Count, 3).End(xlUp))
bln = False: If IsNumeric(rng.Value) Then If Int(rng.Value) =
CDbl(rng.Value) Then bln = True
If Not bln Then rng.Value = ""
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Dimitris" wrote in message
om...
Dear Excel Gurus
I would appreciate your help in composing a macro command that will do
the following:
Imagine that I have a worksheet that contains in column "C" various
7-digit numbers such as for example "2314566"(but in a text format).
Apart from these 7-digit numbers column C contains various other
strings ranging from empty cells to words or a mixture of words and
numbers such as for example "word123" type of strings.
What I would like the macro to do, is to read through the worksheet
(of variable data range each time) and delete entire rows that do not
have in column "C" a pure 7-digit number.Therefore at the end I will
be left with a clean worksheet that will contain only rows that have
7-digit numbers at column C.

Your prompt repsonse will be highly appreciated