VBA: Delete rows on a certain value, issues with spaces

[Pantoffsky]

Hi all,

A part of my VBA script is deleting rows when the line starts with a certain value. It's deleting too much at the moment.

Example: this is my VBA script to delete lines:

Sub DeleteRows()
    Worksheets("Work").Activate

Dim i As Long
    i = Range("A" & Rows.Count).End(xlUp).Row
    With Range("A1:A" & i)
    If Application.WorksheetFunction.CountIf(Columns(1), "     Q*") > 0 Then
    .AutoFilter field:=1, Criteria1:="     Q*"
    .Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete
    .AutoFilter
End If
End With
End Sub

It should delete all lines who start with 5 spaces and then charachter "Q". I'm using the wildcard since there are a lot of lines who start with " Q", this is one of them:

QTYPWUSED Quantity of new sales BXZZ +00000001700 29/09/20 20:31:12

For these lines it works fine.
But the problem is that also these lines are deleted:

Quickscan pre-sales +00000000000 29/09/20 20:31:12

This is not supposed to happen. The "Q" is only after 26 spaces, not after 5 spaces as it was defined in the VBA script.

Anyone a idea how to make the script identify the right rows to delete?

Many thanks!

-Edit: the MrExcel forum is trimming the spaces, so I added the lines as a image. -

 

[JLGWhiz]

Maybe you can use this as a work around. I don't know offhand why the spaces are not recognized as characters by the filter. Haven't read up on that lately.

[CODE=vba]Sub DeleteRows()
Worksheets("Work").Activate
Dim i As Long, c As Range
    i = Range("A" & Rows.Count).End(xlUp).Row
    With Range("A1:A" & i)
    If Application.WorksheetFunction.CountIf(Columns(1), "     Q*") > 0 Then
    .AutoFilter field:=1, Criteria1:="     Q*"
    For Each c In Range("A2:A" & i).SpecialCells(xlCellTypeVisible)
        If InStr(c, "Q") = 6 Then c.Value = True
    Next
    Range("A2:A" & i).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
    '.Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete
    .AutoFilter
End If
End With
End Sub

[/CODE]

 

[Pantoffsky]

Thanks, JLGWhizz

Works fine!

 

[JLGWhiz]

Thanks, JLGWhizz

Works fine!

You're welcome,
regards, JLG