• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 35
  • Last Modified:

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
0
Jen T
Asked:
Jen T
1 Solution
 
ShumsDistinguished Expert - 2017Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now