时间戳

首页 > 解决方案 > 如何在另一个工作簿中查找最后一列

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

问题描述

早上好，朋友

我们如何才能找到最后的活动范围？所以故事：范围是动态的，例如：有时范围（“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

标签： excelvba

解决方案

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

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 行中最后使用的单元格。请注意，在最后使用的单元格之后可能有中间空白，但没有。

推荐阅读