How to Find last columns another workbook

Question

Good morning friends

How can we find the last active range? so the story: The range is dynamic ,,, example: sometimes range ("A2: J2") and sometimes range ("A2: AB2") how to fix this code ?

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

this is my code full

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

user_7675437 · Answer

Insert this snippet into your code (declarations at the top, not between lines of the code).

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

The dynamic range is set from A2 to the last used cell in row 2. Note that there may be intervening blanks but none after the last used cell.

How to Find last columns another workbook

1 Answers