Identify cells based on format
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. |
Identify cells based on format
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 "Alejandro" wrote in message ... 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. |
Identify cells based on format
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" skrev i meddelelsen ... 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. |
Identify cells based on format
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" wrote: 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" skrev i meddelelsen ... 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. |
Identify cells based on format
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 "Alejandro" skrev i meddelelsen ... 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" wrote: 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" skrev i meddelelsen ... 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. |
All times are GMT +1. The time now is 06:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com