excel - 如何查找另一个工作簿的最后一列

Question

friend 们早上好

我们如何找到最后一个事件范围？所以故事:范围是动态的,,,示例:有时是范围(“A2:J2”)，有时是范围(“A2:AB2”)如何修复此代码？

For Each rng In wbk.Sheets(3).Range("A2:J2")    '<<< dynamic range ""   ???? 

这是我的完整代码

Sub try()
Dim fDialog As fileDialog
Dim wbk, Mywbk As Workbook
Dim rng As Range
Dim a As Variant
Dim i, ii, c, r, x, y, z
Set Mywbk = ActiveWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

Set fDialog = Application.fileDialog(msoFileDialogFilePicker)

        
With fDialog

    If .Show = True Then
        Dim fPath As Variant
        fPath = .SelectedItems.Item(1)
        Set wbk = Workbooks.Open(Filename:=fPath)
        
        
    Else
        MsgBox "blank"
        Exit Sub
    End If
    End With
    
Mywbk.Activate
a = Mywbk.Sheets("Sheet1").UsedRange
With CreateObject("scripting.dictionary")
    For i = 1 To UBound(a, 2)
        If Not .exists(a(2, i)) Then
            x = ""
            For ii = 4 To UBound(a)  
                x = x & a(ii, i) & Chr(2)
            Next
            .Add a(2, i), x  
        End If
    Next
    For Each rng In wbk.Sheets(3).Range("A2:J2")    '<<< dynamic range
        c = rng.Column: r = rng.Row   
        y = rng.Value
        x = .Item(y)
        x = Split(x, Chr(2))
        wbk.Sheets(3).Cells(r, c).Offset(1, 0).Resize(UBound(x)) = Application.Transpose(x)
        
    Next
    End With
      
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Answer 1

将此片段插入您的代码(顶部的声明，而不是代码行之间)。

Dim Rng As Range
Dim Cell As Range

With wbk.Worksheets(3)
    Set Rng = .Range(.Cells(2, "A"), .Cells(2, .Columns.Count).End(xlToLeft))
End With

For Each Cell In Rng    '<<< dynamic range
    With Cell
        c = .Column
        r = .Row
        y = .Value
    End With
    x = .Item(y)
    x = Split(x, Chr(2))
    wbk.Sheets(3).Cells(r, c).Offset(1, 0).Resize(UBound(x)) = Application.Transpose(x)
Next Cell

动态范围设置为从 A2 到第 2 行中最后使用的单元格。请注意，中间可能有空白，但在最后使用的单元格之后没有空白。