Excel VBA removing a row if it contains a letter

0
votes

So i have a set of data that has various numbers with letters at the end in example would be

233423528535A
3453453453945
234234235532C
3453453455345
234234238964D
3453453453234
656234234534G
346364634636Z
6456456456452
3453453453242
234234234234X

I want to create a vba script that if a row in this column contains a letter it can be deleted. I tried using an excel function however excel functions dont allow rows to be deleted.

3
vbaexcel
Are teh cells true numbers and text> Are they both right and left aligned in their cells? Are they the General format or formatted as Text? - user4039065
Right(<string> , 1) where string is your Cell.Value - Sorceri
They are left aligned and general text, as well not all of them have the same amount of cahracters in the cell - Saad A

3 Answers

2
votes

Run this short sub to remove all rows containing non-numeric values in column A.

Sub noAlphas()
    On Error Resume Next
    With Worksheets("sheet4")
        .Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
    End With
End Sub
1
votes

Here is my suggestion:

 Sub RemLetterRows()
    Dim DelRow As Range
    With ThisWorkbook.Sheets("Sheet1").Range("A:A") ' Write your sheetname between "" & column
        On Error Resume Next
          Set DelRow = .SpecialCells(xlCellTypeConstants, xlErrors + xlTextValues + xlLogical)
          DelRow.EntireRow.Delete
        On Error GoTo 0
    End With
    End Sub

Regards Daniel

0
votes

If the cells are formatted as text, try to convert them and delete based on the type mismatch error:

Sub noAlphas() On Error Resume Next

For i = 11 To 1 Step -1
    j = CLng(Cells(i, 1).Value)
    If Err.Number = 13 Then Rows(i).EntireRow.Delete

Next

End Sub