Identify cells based on format

[Alejandro]

Hello there,

In several occassions I've tried unsuccessfully to identify specific cells
based on their contents. In this particular case I'm trying to identify (and
delete) the cells of a column that contain numbers instead of text. I tried
using the format property but I didn't get too far. Can anyone help?

Thanks!

AP.

 

[Luke Alcatel]

Depends on what you mean by number. You can test the contents with
IsNumeric which will have a true result for any kind of number such as -1.7.
If you want to limit the test to something more specific such as integers,
look at the "like" function.

HM

 

[Per Jessen]

Hi

Maybe this ?

Sub DeleteNumbers()
For Each cell In Columns("A").Cells
If IsNumeric(cell) = True Then
cell.ClearContents
End If
Next
End Sub

Regards,
Per

 

[Alejandro]

Thanks for the help. Something still doesn't work. When I debug the code I
get an error message stating that cell is a variable not defined. This is the
code I've got:

By the way, I need to delete the whole row, not only the cell. Thanks!!

Sub Macro1()

Dim X, Y As Integer

Y = 0
While Y <= 500
For Each cell In Columns("A").Cells
If IsNumeric(cell) = False Then
X = cell.Row
Worksheets("Sheet1").Rows(X).Delete
Else
cell.Offset(1, 0).Select
End If
Next
Y = Y + 1
Wend

End Sub

 

[Per Jessen]

Hi

Try this:

Sub Macro2()
Dim LastRow As Long
Dim RowCount As Long
Const TargetColumn As String = "A"

RowCount = Columns(TargetColumn).Rows.Count
LastRow = Cells(RowCount, TargetColumn).End(xlUp).Row
For r = LastRow To 1 Step -1
If IsNumeric(Cells(r, TargetColumn)) = False Then
Rows(r).Delete
End If
Next
End Sub

Regards,
Per