[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Overflow problem when filtering

Posted on 2016-10-20
14
Medium Priority
?
112 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 28

Expert Comment

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

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

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

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 33

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
 

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 33

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 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 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 33

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

865 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