CODEWITHSUNDEEP
vbaexcel
Abdullah
Abdullah

Reputation: 2751

How to copy dynamic range in Excel Sheet using vba

I am trying to make the rang is dynamic in the macro without specifying the last line x.Sheets("SheetName").Range("A2:K1000").Copyin 1000 line I want to change it to dynamic because sometimes I have less or more than that.

Upvotes: 2

Views: 20317

Answers (3)

Vityata
Vityata

Reputation: 43595

Something like this will do the job:

Option Explicit

Public Sub TestMe()

    Dim lngLastRow As Long

    lngLastRow = 150
    'or come up with a function from here
    'https://www.rondebruin.nl/win/s9/win005.htm

    With x.Worksheets("SheetName")
        .Range(.Cells(2, 1), .Cells(lngLastRow, 1)).Copy
    End With

End Sub

In general, last row in a given column or last column in a given row is something, that you will do quite a lot of time in VBA. Thus, it is a good idea to read this: https://www.rondebruin.nl/win/s9/win005.htm

Upvotes: 1

user4039065
user4039065

Reputation:

Typically, look for the last row containing a value from the bottom up.

with x.Sheets("SheetName")
    .Range(.cells(2, "A"), .cells(.rows.count, "K").end(xlup)).Copy
    'paste it somewhere
end with

Upvotes: 0

Plagon
Plagon

Reputation: 3137

Try this: 

Sub Test() 
Dim lRow as Long 
Dim sht as Worksheet
Set sht = x.Sheets("SheetName")

lRow = sht.Cells(sht.Rows.Count, 2).End(xlUp).Row

sht.Range("A2:K" & lRow).Copy
End Sub

Upvotes: 4

Related Questions