Get all cels with data from a given range

Question

In my excel, from range A41 to M41 down, i will have data. In my listbox i want to see all data that i will have, not just to row 70 (as my code is). I know i have to use xlUP, but i don't know how. I i will have 500 rows down starting at A41:M41, i have to see them all. Here is my code down to row 70 (specific range):

Private Sub UserForm_Activate()
    Dim lb As msforms.ListBox
    Dim rcArray() As Variant
    Dim lrw As Long, lcol As Long
    Dim rngTarget As Range

    Set rngTarget = Worksheets("Sheet1").Range("A41:M70")

    ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)
    With rngTarget
        For lcol = 1 To .Columns.Count
            For lrw = 1 To .Rows.Count
                rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
            Next lrw
        Next lcol
    End With
    Set lb = Me.ListBox1
    With lb
        .ColumnCount = 13
        .ColumnWidths = "50;80;100"
        .List = rcArray
    End With

Possible duplicate of [Error in finding last used cell in VBA](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Comintern, Sep 12 '16 at 12:59
My question is different and it refers to a Listbox in a Userform. Please remove the duplicate possibility. — Valentin Ivan, Sep 13 '16 at 08:49
Did I miss the question about the Listbox? The accepted answer seems to indicate that it *is* a duplicate. — Comintern, Sep 13 '16 at 12:35

score 1 · Accepted Answer · answered Sep 12 '16 at 14:55

You're actually closer than you think to the answer. This will find the last cell in column A, then makes a string value to set the range to A41:M(LastRow)

Sub Find_the_Last_Row()

Dim lastRow As Long
Dim strRange As String
DimrngTarget As Range

With ActiveSheet
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

strRange = "(A41:M" & lastRow & ")"

Set rngTarget = ActiveSheet.Range(strRange)

End Sub

Get all cels with data from a given range

1 Answers1