VBA Overflow problem when filtering

I am doing a filter to find all of the items with ‘Credit’ then I want to delete these items. The below works fine when there are items in there with ‘Credit in column I. However if there are no items I get ‘Overflow’

Can an expert provide the solution so that if there are no items in column with ‘Credit’ the macro moves on to the next part of the code.

With Range("I1:I" & Cells(Rows.Count, "I").End(xlUp).Row)
            .AutoFilter 9, "=CREDIT"
                         
            If .SpecialCells(xlCellTypeVisible).Count > 1 Then
            .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
            .AutoFilter
    End With


Thanks in advance
JagwarmanAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
if you use...
If .SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
and if column I contains any occurrence of "CREDIT", the following line will delete those rows.
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
1
 
MacroShadowCommented:
Which line does the error occur on?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this to see if that resolves your issue...

 If .SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
JagwarmanAuthor Commented:
If .SpecialCells(xlCellTypeVisible).Count > 1 Then
0
 
JagwarmanAuthor Commented:
Many thanks works a treat
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
 
JagwarmanAuthor Commented:
Actually I was too quick off the mark closing this out

It does work in that it does not fall over but if there are items with 'Credit it does not go to the next line of code which is

.Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If

it goes straight to End If missing out

.Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Since you are filtering only one column you should get an error with the first line filtering the data
.AutoFilter 9, "=CREDIT"
Aren't you getting an error with that line?

I think you should use it like this...
.AutoFilter 1, "=CREDIT"
0
 
JagwarmanAuthor Commented:
The column I am filtering on is column I so that is why I am using .AutoFilter 9, "=CREDIT". If I use .AutoFilter 1, "=CREDIT" that will filter column 'A'
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Your range Range("I1:I" & Cells(Rows.Count, "I").End(xlUp).Row) has only one column i.e. column I, so 9 would be invalid field in this range.
9 is valid if your range being filtered is Range("A1:I" & Cells(Rows.Count, "I").End(xlUp).Row).
I asked you that didn't you get when you used 9 in your code?
0
 
JagwarmanAuthor Commented:
Ah sorry for being dumb. Ok tried that but still when I do it does not go to the next row and delete the records.

.Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
0
 
JagwarmanAuthor Commented:
Thanks again  Subodh Tiwari (Neeraj)
0
 
JagwarmanAuthor Commented:
Again thanks for your assistance
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
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.

All Courses

From novice to tech pro — start learning today.