Solved

VBA Overflow problem when filtering

Posted on 2016-10-20
14
59 Views
Last Modified: 2016-10-24
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
0
Comment
Question by:Jagwarman
  • 7
  • 6
14 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41851598
Which line does the error occur on?
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41851624
Try this to see if that resolves your issue...

 If .SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
0
 

Author Comment

by:Jagwarman
ID: 41851625
If .SpecialCells(xlCellTypeVisible).Count > 1 Then
0
 

Author Comment

by:Jagwarman
ID: 41851627
Many thanks works a treat
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41851629
You're welcome. Glad to help.
0
 

Author Comment

by:Jagwarman
ID: 41851635
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41851646
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Jagwarman
ID: 41851657
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
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41851833
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
 

Author Comment

by:Jagwarman
ID: 41851854
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
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41851868
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
 

Author Comment

by:Jagwarman
ID: 41853334
Thanks again  Subodh Tiwari (Neeraj)
0
 

Author Closing Comment

by:Jagwarman
ID: 41856596
Again thanks for your assistance
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41856610
You're welcome. Glad to help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now