- Sub ABC()
- Dim d, t, i&, arr
- Dim k
- 'k = Timer
- arr = ActiveSheet.UsedRange
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To UBound(arr)
- d(arr(i, 1)) = ""
- Next
- Sheets(2).Activate
- Sheets(1).[1:1].Copy Sheets(2).[a1]
- [a2].Resize(d.Count, 1) = Application.Transpose(d.keys)
- Set d = Nothing
- MsgBox Round(Timer - k, 2)
- End Sub
Please help me explain this syntax： d(arr(i, 1)) = ""
Dictionary objects hold a series of Keys and Values. That line is setting the value for each key as listed in your array to an empty string.
Some useful reading here
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I'm sorry, my English is not very good
I would like to ask is that this paragraph
Is what it means, why is the use of blank strings here?
Dictionary keys are unique. The code will create a unique list by adding them to a dictionary object. The Value is of no importance for this purpose and an empty string is used. You could use any value in this case; d(arr(i, 1)) = "zzz" would work just as well.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Can I ask further, for learning EXCEL VBA, what advice do you have?
Search VBA for Beginners. There are many resources available. Use the macro recorder (I stll do) and edit the code to suit your purpose.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
How to learn the correct syntax of vba?
Use Option Explicit and Intellisense. Check the tickboxes in Tools/Options
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I should not express it very clearly, what i mean is how to use the correct and standard syntax.
By using Option Explicit at the top of your code, forces the coder to define your variables correctly. Intellisense when used allows excel to prompt the user with a selection of correct syntax.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Can this 「d(arr(i, 1)) = ""」 be replaced in another way? For example 「If d.Exists.(arr(i, 1)) Then........EndIf」
For what purpose?
The line creates the key when it adds the null value, although it could be shown with a different syntax. I refer you to the reading in Post #2
Is this a school exercise or similar?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Do u mean that d(arr(i, 1)) = "" (or d(arr(i, 1)) = "zzz") is the only way of dictionary for solving duplicated case problem?
I don't see a problem. Dictionary keys cannot be duplicate; Dictionary values can be.
Without knowing what you're trying to do, we can't assist. I repeat; Is this a school exercise or similar?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Alternative method
you can use Advanced Filter manually.
if vba, please try this.
Option Explicit Sub test() Dim r As Range, t As Range Set r = Sheets(1).UsedRange.Columns(1) Set t = Sheets(2).Cells(1) r.AdvancedFilter xlFilterCopy, , t, True Application.Goto t End Sub
Posting Permissions
Reply With Quote
