Excel VBA技术问询：提取区域数据存数组排序及获取列唯一值存数组

Hey there! Welcome to Stack Exchange—glad you're here. Let's break down your two Excel VBA needs step by step, with clear code examples you can tweak for your use case:

There are two common scenarios here: working with a single-column/row range, or a multi-column range. Let's cover both:

Scenario 1: Single-column range (e.g., A1:A10) #

First, we'll pull the range data into an array, then use a simple bubble sort to order it:

Sub GetAndSortSingleColumnData()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim dataArr As Variant
    Dim i As Long, j As Long
    Dim temp As Variant
    
    ' Set your target worksheet and range (adjust these to match your file)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set dataRange = ws.Range("A1:A10")
    
    ' Load range data into a 2D array (Range.Value returns 2D by default)
    dataArr = dataRange.Value
    
    ' Convert to 1D array for easier sorting
    dataArr = Application.Transpose(dataArr)
    
    ' Bubble sort the 1D array (ascending order)
    For i = LBound(dataArr) To UBound(dataArr) - 1
        For j = i + 1 To UBound(dataArr)
            If dataArr(i) > dataArr(j) Then
                temp = dataArr(i)
                dataArr(i) = dataArr(j)
                dataArr(j) = temp
            End If
        Next j
    Next i
    
    ' Optional: Verify the sorted array (prints to Immediate Window)
    For i = LBound(dataArr) To UBound(dataArr)
        Debug.Print dataArr(i)
    Next i
End Sub

Scenario 2: Multi-column range (e.g., A1:C10) #

If you need to sort by a specific column, it's often easier to sort the cell range first, then load it into the array:

Sub GetAndSortMultiColumnData()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim dataArr As Variant
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set dataRange = ws.Range("A1:C10")
    
    ' Sort the range first (sort by column 1, ascending; use xlYes if you have headers)
    dataRange.Sort Key1:=dataRange.Columns(1), Order1:=xlAscending, Header:=xlNo
    
    ' Load the sorted range into the array
    dataArr = dataRange.Value
    
    ' Now dataArr holds the sorted multi-column data
End Sub

The easiest way to handle unique values in VBA is with a Dictionary object—it automatically ignores duplicates. Here's how to do it (no extra references needed, using late binding):

Sub GetUniqueValuesFromColumnC()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim uniqueDict As Object
    Dim uniqueArr As Variant
    Dim item As Variant
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' Create a Dictionary instance
    Set uniqueDict = CreateObject("Scripting.Dictionary")
    
    ' Find the last row with data in Column C
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    
    ' Loop through Column C and add non-blank, non-duplicate values to the Dictionary
    For Each cell In ws.Range("C1:C" & lastRow)
        If cell.Value <> "" And Not uniqueDict.Exists(cell.Value) Then
            uniqueDict.Add Key:=cell.Value, Item:=cell.Value
        End If
    Next cell
    
    ' Convert the Dictionary's unique keys into an array
    uniqueArr = uniqueDict.Keys
    
    ' Optional: Verify the unique array (prints to Immediate Window)
    For Each item In uniqueArr
        Debug.Print item
    Next item
    
    ' Now you can reuse uniqueArr elsewhere in your code!
End Sub

A quick note: The uniqueArr will be a 1D array. If you need to sort it, just use the bubble sort code from the first section.

内容的提问来源于stack exchange，提问作者chiko42