Excel - Extract only numeric fields from a selection containing texts and numbers

A solution in VBA would be to use the procedure below. Note that it is generic, not just for what is selected. You pass an object of type Range and it performs the iteration through all the cells that make up that range.

Sub excluiLinhasNaoNumericasOuVazias(range As range)
      Dim celulasParaDeletar As Scripting.Dictionary
      Dim r As range

      Set celulasParaDeletar = New Scripting.Dictionary

      For Each r In range
         If Not IsNumeric(r.Value) Or r.Value = "" Then
             celulasParaDeletar.Add CStr(r.Row), r
          End If
      Next

      For i = 0 To celulasParaDeletar.Count - 1
          celulasParaDeletar.Items(i).Delete
      Next
End Sub

This solution uses a dictionary-type object to store rows that need to be deleted. You can not exclude within a loop that iterates over the cells, because deleting a row changes the original range passed as a parameter. Consequently, some lines that need to be deleted are not.

To use the Scripting.Dictionary class, you must include a reference called Microsoft Scripting Runtime.

The isNumeric () function is used to determine whether the content of a cell is a number or not. Empty cells are considered numbers by Excel, so it is necessary to test if the content is empty.

Below is a test for this solution:

Sub teste()
    excluiLinhasNaoNumericasOuVazias Selection
End Sub

Assume that cells need to be selected in the worksheet, since an object of type Selection is being passed as a parameter.

Solution found:

Function deleteString(ByVal STRING_TO_BE_DELETED As String)
'
' Deleta um texto específico
' IMPORTANTE: A origem já deve estar selecionado
'
    Selection.Replace What:=STRING_TO_BE_DELETED, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Function

Function deleteBlank(Optional NOT_IN_USE As Boolean)
'
' Deleta espaços em brancos
' IMPORTANTE: Os dados já devem vir selecionados
'
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.delete
End Function

Function deleteString_Blank(ByVal RANGE_SELECTION As Range, ByVal LIST_TO_BE_DELETED As String)
'
' Deleta texto de uma seleção
'
Dim arr As Variant
Dim i As Integer
    RANGE_SELECTION.Select
    arr = Range(LIST_TO_BE_DELETED)
    For i = LBound(arr, 1) To UBound(arr, 1)
        deleteString arr(i, 1)
    Next i
    deleteBlank
End Function

deleteString_Blank Columns(1), "NOME_DA_LISTA"

Remembering that the number '1' in Columns(1) is the selected column and the '_NAME_NAME' should be the named range containing the data that should be deleted from the data ranges. In my case the list is a table with the following options, following the example above (of the question):

  

Good

     

Bad

     

Great

I hope I have helped!

You can also try to use another formula, I do not know why, whenever I use SpecialCells(xlCellTypeBlanks) , it gives error - So I chose to try for autofilter :

Sub seleciona_numeros()
Dim ultimalinha As Integer

'identifica ultima linha
ultimalinha = Range("A" & Rows.Count).End(xlUp).Row

'escreve COL2 na coluna B e verifica o que é numero na coluna A
    Range("B1").FormulaR1C1 = "COL2"
    Range("B2").FormulaR1C1 = "=IF(ISNUMBER(RC[-1]),RC[-1],"""")"

'aplica a formula até a ultima linha
    Range("B2").Copy
    Range("B2:B" & ultimalinha).PasteSpecial xlPasteAll
    Application.CutCopyMode = False

'exclui as formulas colando como valores
    Range("B2:B" & ultimalinha).Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

'filtra e exclui as linhas vazias

    Range("B:B").Select
    ActiveSheet.Range("$A$1:$B$" & ultimalinha).AutoFilter Field:=2, Criteria1:="="

    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    ActiveSheet.ShowAllData

End Sub