Macro to delete rows where text starts with a ~

[howard]

I have the following sample data below

I have written code to delete the rows where the data contains a ~, but the code does not delete any rows where the txt contains a ~

Book1
	A
1	M_BR1.xlsm
2	~$M_BR1.xlsm
3	M_BR2.xlsm
4	~$M_BR2.xlsm
Workspace

 Sub Del_Unwanted()
Sheets(1).Select

Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
 If Range("A" & i).Value = "~" Then Range("A" & i).EntireRow.Delete
Next i
End Sub

It would be appreciated if someone could amend my code

 

[Fluff]

Try

 If Left(Range("A" & i).Value, 1) = "~" Then Range("A" & i).EntireRow.Delete

 

[howard]

Thanks for the help. Code works perfectly

 

[Rick Rothstein]

 Sub Del_Unwanted()
Sheets(1).Select

Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
 If Range("A" & i).Value = "~" Then Range("A" & i).EntireRow.Delete
Next i
End Sub

Assuming the values in Column A as constants (that is, not formulas), then here is another way to write your code (it does not use a loop)...

[table="width: 500"]
[tr]
	[td]Sub Del_Unwanted()
  On Error GoTo NoRowsToDelete
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("IF(LEFT(@)=""~"",""#N/A"",IF(@="""","""",@))", "@", .Address))
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
NoRowsToDelete:
End Sub[/td]
[/tr]
[/table]

 

[howard]

Thanks for the help Rick.