Excel VBA not deleting all filtered rows

hi there,

I have a little Excel VBA code that autofilters for a criteria and then deletes those that meet that criteria.

Here is the code:
  With rngData
            .AutoFilter Field:=8, Criteria1:=Array("*Transfer"), Operator:=xlFilterValues
                       
            'Delete the visible rows while keeping the header
                If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
                 .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
                Else
                End If
        End With

Open in new window


This code was working fine until yesterday. For some reason, it stopped deleting all the visible rows. I'm not sure why.
I have tried replacing .Rows.Delete to .EntireRow.Delete but it doesn't work either.
I'm baffled as this was working fine (for weeks now) until yesterday. This code is applied to an automated report that is sent out daily.

Can anyone please tell me what could have gone wrong? It seems to be deleting the top half, then leaving the bottom half of the filtered rows.

Thanks in advance
Jen TAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi Jen,

Try below:
With rngData
    .AutoFilter
    .AutoFilter Field:=8, Criteria1:=Array("*Transfer"), Operator:=xlFilterValues
    If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
        .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
    .AutoFilter
End With

Open in new window

Alos check the rngData range if you are defining actual last row
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jen TAuthor Commented:
Thank you so much for your help.

It was actually your suggestion to check rngData that was the issue.
The sheet it pulls in seems to have hidden rows so the last row was incorrect.
I have just added a line to unhide all rows to prevent it from happening in the future.

Again, thank you for your prompt response. You're awesome!

Jen
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.