Out of range error in VBA

Question

1.00/5 (1 vote)

See more:

i want to write macro for splitting the data from master data file into different sheets. I have written below codes but its showing out of range error. can you please help?

Sub SplitandFilterSheet()
'step 1 - Copy sheet
'Step 2 - Filter by Department and delete rows not applicable
'step 3 - Loop until the end of the list

Dim Splitcode As Range
Sheets("Master").Select
Set Splitcode = Range("SplitCode")

For Each cell In Splitcode
Sheets("Master").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = cell.Value

With ActiveWorkbook.Sheets(cell.Value).Range("MasterData")
.AutoFilter Field:=5, Criterial:="<>" & cell.Value, Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

ActiveSheet.aitofilter.ShowAllData
Next cell
End Sub

What I have tried:

I tried many things but its not working. can someone pleas help?

Posted 8-Oct-18 0:58am

Member 14011636

Updated 8-Oct-18 22:38pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-10-08T01:07:00

We can't help much - this requires your data and your code running to work out what the problem might be, and we don't have access to either.

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. MS Excel 2013: VBA Debugging Introduction[^] should give you the info you need to ghet started.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!

Patrice T · Answer 2 · 2018-10-08T18:42:00

Quote:
I tried many things but its not working. can someone pleas help?

Your code do not behave the way you expect, or you don't understand why !

There is an almost universal solution: Run your code on debugger step by step, inspect variables.
The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't know what your code is supposed to do, it don't find bugs, it just help you to by showing you what is going on. When the code don't do what is expected, you are close to a bug.
To see what your code is doing: Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute.
Debugger - Wikipedia, the free encyclopedia[^]
Debugging in Excel VBA - EASY Excel Macros[^]
MS Excel 2013: VBA Debugging Introduction[^]
How to debug Excel VBA - YouTube[^]

The debugger is here to only show you what your code is doing and your task is to compare with what it should do.

Maciej Los · Answer 3 · 2018-10-08T22:38:00

There migth be few reason of Subscript out of range error message. See: Subscript out of range (Error 9) | Microsoft Docs[^]

On the first look, you're trying to change collection in for each loop, which is the main reason of above error.

In addition, i'd avoid of using Select/Activate method due to performance. See: How to avoid using Select in Excel VBA[^].

Out of range error in VBA

3 solutions

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0