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

Pantoffsky

New Member
Joined
Jan 26, 2016
Messages
10
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:
VBA Code:
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:

1601413228535.png

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:

1601413276303.png

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. -
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,517
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
[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]
 

Watch MrExcel Video

Forum statistics

Threads
1,113,930
Messages
5,545,083
Members
410,652
Latest member
Zot
Top