VBA Removing and shifting up cells based on leftmost value

[KasperC]

Hello again,

From what I can see I've copied codes that should work from different posts like this one, but I still can't get it to remove the rows in question.

I want to remove rows that has a number in the A column that either starts with a "2000" or "29" ex.: "20004576" or "2998372819" and then shift all the data up.
Right now it runs without issiues, the filter works, but it doesnt remove anything from the sheet.

Private Sub Tspar()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim adato As String
    Dim LastRow As Long
   
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Inn")
    LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    adato = Range("L1").Value
   
    Application.ScreenUpdating = False
   
    ws.Range("C1").AutoFilter Field:=3, Criteria1:=adato
        Range("L1").Value = ""
   
    LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
   
        For i = LastRow To 2 Step -1
            If Left(Cells(i, 1), 1) = "2000" _
            Or Left(Cells(i, 1), 1) = "29" _
            Then
                Cells(i, 1).EntireRow.Delete Shift:=xlUp
            End If
        Next
 
    Application.ScreenUpdating = True
End Sub

Any ideas?

Thank you,

Regards,
Kasper C

*edit: just wording correction

 

[Joe4]

There seems to be errors in the second argument of your LEFT functions:

            If Left(Cells(i, 1), 1) = "2000" _
            Or Left(Cells(i, 1), 1) = "29" _

It should be:

            If Left(Cells(i, 1), 4) = "2000" _
            Or Left(Cells(i, 1), 2) = "29" _

 

[KasperC]

There seems to be errors in the second argument of your LEFT functions:

            If Left(Cells(i, 1), 1) = "2000" _
            Or Left(Cells(i, 1), 1) = "29" _

It should be:

            If Left(Cells(i, 1), 4) = "2000" _
            Or Left(Cells(i, 1), 2) = "29" _

Oha, so one has to specify how many characters one wants to look. Interesting.

Again, thank you so much!

 

[Joe4]

You are welcome.

Yes, that is true for LEFT, RIGHT, and MID.

See: LEFT Function