copy rows where column data begins with an alpha character

[shaz0503]

Hi all

Am trying to set up some macros to 'clean data'.

I have some in place but am stuck with one that will identify where any address in column "g" that begins with anything other than numeric value copies the entire row to sheet 2...obviously where there is more than one that each copied row is in a new row......

TIA

Shaz

[berlan]

Without fully understanding how this would improve, perhaps this might help?

Sub main()

    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets(1)
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets(2)
    Dim c As Range, i As Integer

    For Each c In ws1.Range("G1", ws1.Range("G" & Rows.Count).End(xlUp))
        If c.Value Like "[A-Z]*" Or c.Value Like "[a-z]*" Then
            i = i + 1
            c.EntireRow.Copy ws2.Cells(i, 1)
        End If
    Next c

End Sub

[shaz0503]

Berlan

Works perfectly - and now I have a base, hopefully I can utilise this further....


rgds and happy holidays

[berlan]

You're welcome @shaz0503, and thanks for the rep! Happy holidays!

[jindon]

Why use of "Or"?
change to

        If c.Value Like "[A-Za-z]*" Then