Delete cells with Numbers and Text 3

[Mantle51]

I have the following which attempts to keep only text, everything else should be deleted, but I can't get it working. Any suggestions are most appreciated

If ActiveCell.Text > 0 And IsNumeric(ActiveCell.Value) Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Text > 0 Then
ActiveCell.Offset(1, 0).Select

End If

 

[firefytr]

Hi there,

What is the range you are looking at?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Mantle51]

Hello,

Range is A1:A?? - it will vary depending on output.

thanks.......

 

[SkipVought]

Hi

'keep text only
with ActiveCell
  if isnumeric(.value) then .entirerow.delete
  .offset(1).select
end with

I suggest NOT using the Activecell method, however...

with cells(lrow, icol)
  if isnumeric(.value) then .entirerow.delete
  lrow = lrow + 1
end with

Skip,

_[red][/red]

 

[firefytr]

How about something like this ...

Sub LeaveOnlyTextInA()
    Dim i as long
    application.displayalerts = false
    for i = cells(rows.count, 1).end(xlup).row to 1 step -1
        if isnumeric(cells(i, 1)) then
            cells(i, 1).entirerow.delete
        end if
    next i
    application.displayalerts = true
End Sub

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Mantle51]

OK I should've specified:

Some cells will contain both text and number, eg mailing address - these need to be deleted as well as other cells with just numbers. Only the names should remain.

Thanks.........Mickey

 

[firefytr]

Okay, then maybe ...

Sub LeaveOnlyTextInA()
    Dim i as long
    application.displayalerts = false
    for i = cells(rows.count, 1).end(xlup).row to 1 step -1
        if TestForText(cells(i, 1).Value) then
            cells(i, 1).entirerow.delete
        end if
    next i
    application.displayalerts = true
End Sub

Function TestForText(varVal as variant) as boolean
    Dim i as long
    TestForText = False
    For i = 1 to len(varVal)
        If Asc(Mid(varVal, i, 1)) > 47 And Asc(Mid(varVal, i, 1)) < 58 Then
            TestForText = True
            Exit For
        End If
    Next i
End Function

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[PHV]

Why not simply this ?

Sub LeaveOnlyTextInA()
    Dim i As Long
    Application.DisplayAlerts = False
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        If [!]Cells(i, 1).Value Like "*[0-9]*"[/!] Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next i
    Application.DisplayAlerts = True
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mantle51]

Thanks PH:
I was trying some kind of

dim AnyNum(0-9)
then from the if statement trying to get a wildcard to recognize AnyNum....but no luck....I came up with the following. Had to add that elseif b/c the delete row messed up the confluence of offsets.


Sub PurgeNum()

Dim i As Integer

Range("A1").Select

Do Until ActiveCell.Value = ""
For i = 0 To 9
If InStr(ActiveCell.Value, i) > 0 Then
Selection.EntireRow.Delete
Exit For
ElseIf i = 9 Then
ActiveCell.Offset(1, 0).Select
End If
Next i
Loop


End Sub