delete row if cell in column contains text

[dialup]

Hi clever people, i know this has to be easy but i just can't get it.
I have a sheet full of data, column c should be all numbers but sometimes contains text instead, i need a macro to check column c and if the cell contains text instead of a numer to delete the whole row, the number of rows changes.

thanks in advance

 

[jbeaucaire]

This little macro will do it. Just adjust the Sheet2 name to your own sheetname.

Option Explicit
Sub TextDeleteC()
Dim cell As Range, rng As Range
Dim lastrow As Long

lastrow = Sheets("Sheet2").UsedRange.Rows.Count

Set rng = Sheets("Sheet2").Range("C2:C" & lastrow)

    For Each cell In rng
        If Not Application.WorksheetFunction.IsNumber(cell.Value) Then
        cell.EntireRow.Delete
        End If
    Next cell
End Sub

Alt-F11 to open the VBEditor
Click on Insert > module
Paste in the code above
Alt-F11 to close the editor
Save your sheet.

Run the macro.

 

[jbeaucaire]

Just a wee shorter...basically the same macro:

Option Explicit
Sub TextDeleteC()
Dim cell As Range, rng As Range
Dim lastrow As Long

lastrow = Sheets("Sheet2").UsedRange.Rows.Count

Set rng = Sheets("Sheet2").Range("C2:C" & lastrow)

    For Each cell In rng
        If Not Application.WorksheetFunction.IsNumber(cell.Value) Then cell.EntireRow.Delete
    Next cell
End Sub

 

[dialup]

Thanks - as ever works purfectly - greatly appreciated