Deleting row when condition is met

[Gokuba]

HI,

I have a row of data and want to delete a row if the condition is true. What is wrong with my code?




Sub deltotal()
Lastr = Cells(rows.Count, "A").End(xlUp).row
ctes = Range(x, 1)
For x = 1 To Lastr Step 1
If Application.WorksheetFunction.Left(ctes, 18) = "Total for Customer" Then
row.Delete




Next x
Else
MsgBox ("NONON")








End Sub

 

[JustynaMK]

Hi,

When building your VBA code I recommend using indentation so that you make sure your IF Statement is properly structured:

Sub deltotal()
    Dim Lastr               As Long
    Dim x                   As Long


    Lastr = Cells(Rows.Count, "A").End(xlUp).Row


    For x = Lastr To 1 Step -1
        If Left(Range("A" & x).Value, 18) = "Total for Customer" Then
            Range("A" & x).Rows.Delete
        Else
            MsgBox ("NONON")
        End If
    Next x


End Sub

Also, because you are going to delete your rows, it's better to start your For...Next statement from the last row and then use Step -1.

Hope it helps!

 

[Yongle]

Try this

Sub deltotal()
    Dim lastR, ctes As Range, x As Long
    With ActiveSheet
        lastR = .Cells(Rows.Count, "A").End(xlUp).Row
        For x = lastR To 1 Step -1
            Set ctes = .Cells(x, 1)
            If Left(ctes, 18) = "Total for Customer" Then
                ctes.EntireRow.Delete
            Else
                'do nothing
            End If
        Next x
    End With
End Sub

What is wrong with my code?

A few pointers...

Dim lastR, ctes As Range, x As Long
always declare your variables
- some of your errors become easier to spot when code stops due to variable type being wrong etc

ctes = Range(x, 1)
use (x,1) notation with Cells not Range
also, a Range must be set

Either
Set ctes = Cells(x, 1)   
or 
Set ctes = Range("A" & x)

row.Delete
must tell VBA which row

ctes.EntireRow.Delete

IF Application.WorksheetFunction.Left(ctes, 18) =
Some things are using wotksheet functions others are VBA functions in their own right
Sometimes names differ (CHAR in Excel vs CHR in VBA) , but sometimes (eg here!) the names are the same
You have to learn which is which

IF Left(ctes, 18) = "Total for Customer" Then

For x = lastR To 1 Step -1
start at bottom and count backwards when deleting rows
(otherwise row numbers are changing ahead of the code)

 

[Gokuba]

Thank you Both, it solved all my code problems and taught new things. Problem solved.
THANK YOU SO MUCH ( and Dziekuje Justyna )

 

[JustynaMK]

nie ma problemu! Powodzenia