Array question for Excel VBA
I need to build an array that only should contain unique strings that must not exist more the once in the array. (even if the range can include duplicates of the string)
I have a range A1:A100 to conatins strings (names to other sheets in a Excel workbook).
I need to filll an array with all the strings in this range, but there must NOT be any duplicates of the strings. And I also need to check if the string in each cell of the range is a name to a worksheet that exist.
Does anyone know how to do this in VBA?
I am a delphi coder, and how not found any good VBA reference.
Example:
Lets say I have a range in Excel that looks like this:
A B C D
1 Sheet1
2 Sheet2
3 Sheet1
4 Sheet4
5 Sheet3
6 Sheet2
7 MySheet
8 TestSheet
9 Sheet2
Lets say that my Excel Workbook has a coresponding sheet for each of this strings, except for "TestSheet" in cell A8. I now need to build an array that looks like:
Array ("Sheet1","Sheet2","Sheet4
A unique string should only exist once in the array and if there is no worksheet with the same name as the string, it should also not be included in the array.
Can all this be done with VBA in Excel?
Best Regards
Magnus Flysjö
I have a range A1:A100 to conatins strings (names to other sheets in a Excel workbook).
I need to filll an array with all the strings in this range, but there must NOT be any duplicates of the strings. And I also need to check if the string in each cell of the range is a name to a worksheet that exist.
Does anyone know how to do this in VBA?
I am a delphi coder, and how not found any good VBA reference.
Example:
Lets say I have a range in Excel that looks like this:
A B C D
1 Sheet1
2 Sheet2
3 Sheet1
4 Sheet4
5 Sheet3
6 Sheet2
7 MySheet
8 TestSheet
9 Sheet2
Lets say that my Excel Workbook has a coresponding sheet for each of this strings, except for "TestSheet" in cell A8. I now need to build an array that looks like:
Array ("Sheet1","Sheet2","Sheet4
A unique string should only exist once in the array and if there is no worksheet with the same name as the string, it should also not be included in the array.
Can all this be done with VBA in Excel?
Best Regards
Magnus Flysjö
ASKER
is it possible to put the result into an array instead of in cells?
Range("A1:A100").AdvancedF
Like this (into MyArray):
Range("A1:A100").AdvancedF
Range("A1:A100").AdvancedF
Like this (into MyArray):
Range("A1:A100").AdvancedF
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
No, this isn't possible. you have first to Filter to a Range (maybe you can use a temporary workbook only for that), and then build your array from the results.
CHeers
CHeers
You'll have some example code in 5 minutes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I write it in a hurry, there may be some bugs :-(
But I have to go home and had no time for more...
But I have to go home and had no time for more...
ASKER
Great Job...
It worked perfect...
It worked perfect...
Data->Filter->Advanced Filter
*Choose the range you want to filter (ie A1:A100),
*choose the destination (eventually same range...)
*choose unique records only
click OK
CHeers