VBA Macro to delete row based on data value

[TheCobbler]

Hi, I'm looking for some help with a VBA macro I have.

I'm trying to delete any row that doesn't start with a 1 or 2 in column A.
This data changes daily so the amount of rows varies.

The code I have below only seems to do what I need on a sporadic basis and after breaking it down for hours I'm still unable to get it to work... I have also tried other method which have also failed! Please point me in the correct direction if there's a better way.

Thanks in advance, C

Example Data:

Current cribbed code - admittedly it may be a tad advanced for me at the moment.

Sub TrckMac ()

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim SearchRange As Range, SearchCell As Range, DeleteMe As Range
    
    Set SearchRange = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
    
    For Each SearchCell In SearchRange
        If Left(SearchCell, 1) = 6 Or Left(SearchCell, 1) = "m" Then
            If DeleteMe Is Nothing Then
                Set DeleteMe = SearchCell
            Else
                Set DeleteMe = Union(DeleteMe, SearchCell)
            End If
        End If
    Next SearchCell
    
    If Not DeleteMe Is Nothing Then DeleteMe.EntireRow.Delete

End Sub

 

[dmt32]

Hi,
quick glance cannot see anything wrong with code BUT your coded search values differ to your stated requirement

If Left(SearchCell, 1) = 6 Or Left(SearchCell, 1) = "m" Then

If you want values 1 or 2 to be tested then need to update line shown above with those values.

 If Left(SearchCell, 1) <> 1 Or Left(SearchCell, 1) <> 2 Then

maybe?

Dave

 

[TheCobbler]

Thanks Dave, I've switched these around and given it a go in a selection of books and new subs.

The code seems to be doing noting at all at this point.

 

[veyselemre]

        If Not (Left(SearchCell, 1) = 1 Or Left(SearchCell, 1) = 2) Then

 

[dmt32]

Thanks Dave, I've switched these around and given it a go in a selection of books and new subs.

The code seems to be doing noting at all at this point.

sorry did not test but try

Sub TrckMac()

    Dim SearchRange As Range, SearchCell As Range, DeleteMe As Range
    Dim ws As Worksheet
   
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Set SearchRange = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
   
    For Each SearchCell In SearchRange
        If Left(SearchCell, 1) = 1 Or Left(SearchCell, 1) = 2 Then
            'do nothing
        Else
            If DeleteMe Is Nothing Then
                Set DeleteMe = SearchCell
            Else
                Set DeleteMe = Union(DeleteMe, SearchCell)
            End If
        End If
    Next SearchCell
   
    If Not DeleteMe Is Nothing Then DeleteMe.EntireRow.Delete

End Sub

Dave

 

[TheCobbler]

Nothing happening again. Sorry!

 

[JEC]

If you want to delete rows, always start at the bottom.

This will do(maybe change sheet names in the code)

Sub tst()
Set ar = Sheets(1).Range("A2", Sheets(1).Cells(Rows.Count, 1).End(xlUp))
  For i = ar.Rows.Count To 1 Step -1
     If Left(ar(i, 1), 1) = 1 Or Left(ar(i, 1), 1) = 2 Then ar(i, 1).EntireRow.Delete
  Next
End Sub

 

[dmt32]

Nothing happening again. Sorry!

no need to be sorry

just a guess but try updating the line with this

If Trim(Left(SearchCell, 1) = 1) Or Trim(Left(SearchCell, 1) = 2) Then

Dave

 

[TheCobbler]

If you want to delete rows, always start at the bottom.

This will do(maybe change sheet names in the code)

Sub tst()
Set ar = Sheets(1).Range("A2", Sheets(1).Cells(Rows.Count, 1).End(xlUp))
  For i = ar.Rows.Count To 1 Step -1
     If Left(ar(i, 1), 1) = 1 Or Left(ar(i, 1), 1) = 2 Then ar(i, 1).EntireRow.Delete
  Next
End Sub

Works perfectly thanks!

 

[TheCobbler]

no need to be sorry

just a guess but try updating the line with this

If Trim(Left(SearchCell, 1) = 1) Or Trim(Left(SearchCell, 1) = 2) Then

Dave

Gave it a go but no luck. Will take the approach from JEC above. Thanks again for your help!