Q:

VBA: For loop that does not want to iterate more than once

I have written a simple code to delete the lines of my Excel worksheet where the value is not numerical and where there is no value. But, weirdly enough, the loop executes only once when I run the program.
How can I solve this so that my program deletes all at once the lines that meet my two conditions?
Sub foo()

Dim lRow As Integer
Dim sht As Worksheet
Set sht = ActiveWorkbook.ActiveSheet

lRow = sht.Range("A" & Rows.Count).End(xlUp).Row

For Each c In Range(sht.Cells(2, 1), sht.Cells(lRow, 1))

If (Not IsNumeric(c.Value) Or c.Value = "") Then c.EntireRow.Delete

Next

End Sub

A:

This uses AutoFilter to remove targeted rows in 2 steps:
Criteria1:="=*" shows non-empty strings, Criteria2:="=" shows empty values

Option Explicit

Public Sub foo()
    Application.ScreenUpdating = False
    With ActiveWorkbook.ActiveSheet.UsedRange

        'Step 1 - Remove all strings and empty values:
        .AutoFilter field:=1, Criteria1:="=*", Operator:=xlOr, Criteria2:="="
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete     'Excludes the header row

        'Step 2 - Remove all numbers that are not 6 digits in length:
        .AutoFilter field:=1, Criteria1:="<100000", Operator:=xlOr, Criteria2:=">999999"
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete     'Excludes the header row

        .AutoFilter 'Removes filter
    End With
    Application.ScreenUpdating = True
End Sub

Edit:
The above version will exclude the header row, which becomes the AutoFilter row (with the arrow)
If there is no header row there are more checks to be done.
For example with this data:
100,000
100,001
100,003

The first visible cell (not included in the filter) will be 100,000 which shouldn't be deleted
If the data is:
Abc
100,000
100,001
100,003

The first visible cell (not included in the filter) will be Abc which should be deleted
So version 2 (bellow) addresses this issue:

Option Explicit

Public Sub foo()
    Dim rowsToDelete As Range

    Application.ScreenUpdating = False
    With ActiveWorkbook.ActiveSheet.UsedRange

        'Step 1 - Remove all strings and empty values:
        .AutoFilter Field:=1, Criteria1:="=*", Operator:=xlOr, Criteria2:="="
        Set rowsToDelete = CheckFirstCell(.Columns(1))
        If Not rowsToDelete Is Nothing Then rowsToDelete.EntireRow.Delete

        'Step 2 - Remove all numbers that are not 6 digits in length:
        .AutoFilter Field:=1, Criteria1:="<100000", Operator:=xlOr, Criteria2:=">999999"
        Set rowsToDelete = CheckFirstCell(.Columns(1))
        If Not rowsToDelete Is Nothing Then rowsToDelete.EntireRow.Delete

        .AutoFilter 'Removes filter
    End With
    Application.ScreenUpdating = True
End Sub

Private Function CheckFirstCell(ByRef rng As Range) As Range    'It can return Nothing
    If Not rng Is Nothing Then
        Dim tmp As Variant
        With rng
            .SpecialCells(xlVisible).Select
            tmp = Selection(1).Value2
            If Not IsNumeric(tmp) Or (tmp < 100000 Or tmp > 999999) Or Len(tmp) = 0 Then
                Set CheckFirstCell = .EntireRow
            End If
            If Selection.Count > 1 Then
                If CheckFirstCell Is Nothing Then
                    Set CheckFirstCell = .Offset(1).Resize(.Rows.Count - 1).EntireRow
                Else
                    Set CheckFirstCell = .EntireRow
                End If
            End If
            .Cells(1).Select
        End With
    End If
End Function

