Adaptive cell range

**Trevasaurus** · 05-10-2013, 02:46 PM

Hey,

I'm trying to get a cell range to increase to match my data size, every time the macro is ran.

Basically, I have a range ("B2:K20") that I'm copying and pasting over one column, to ("C2:L20").

Now, the next time I run the macro to copy and paste this range, the range I want to capture is 1 column larger. So the next range, after copying and pasting the first range to the right, would be ("B2:L20").

How can I make my range equal the number of columns I want to capture? It might help to add that there is nothing to the right of the cell range I'm trying to target.

Thanks in advance

**NeedForExcel** · 05-10-2013, 03:38 PM

Hey!!

You will have to find the Last Column. You can use -

Dim LC as Integer

LC = Cells(2,Columns.Count).End(XLToLeft).Column

Then you can refer the Range using LC in place of Column References.

Thank You,

Deep

**Trevasaurus** · 05-13-2013, 12:04 PM

Hey ExcelatHome

Thanks for the code. I'm trying to insert it into my macro but there's something wrong with my syntax.
I'm not sure how to add that integer into my cell range. Any help is appreciated. This is what I have so far.

Sub Roll_Graph_Data()
'
' Range_Test Macro

    Dim LC As Integer
    LC = Cells(2, Columns.Count).End(xlToLeft).Column
    
    Range("K5:VLC").Select
    Selection.Copy
    Range("L5").Select
    ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
        IconFileName:=False
    Sheets("Control").Select
End Sub