Pantoffsky
New Member
- Joined
- Jan 26, 2016
- Messages
- 14
- Office Version
- 2019
- Platform
- Windows
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:
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. -
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:
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. -