CODEWITHSUNDEEP
excelvba
Red
Red

Reputation: 161

storing a column within another variable in VBA

I am trying to store a variable name firstRng into

lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A"& lMaxRows + 1).Select

in place of "A"

here is what I have tried


Dim entireRange As Range
Dim firstRng As Range
Dim secondRng As Range

Set entireRange = Range("A2:C2")
Set secondRng = Range("B2")
Set firstRng = range("A:A")

    entireRange.Select
    secondRng.Activate
    Selection.Copy
    lMaxRows = Cells(Rows.Count, firstRng).End(xlUp).Row
    Range(firstRng & lMaxRows + 1).Select
    ActiveSheet.Paste

it will error on 1maxRows "Type mismatch"

Upvotes: 0

Views: 268

Answers (1)

BigBen
BigBen

Reputation: 49998

firstRng is a multi-cell Range. You're implicitly working with its .Value, and not with relevant properties such as its .Column.

You should also avoid Activate and Select:

lMaxRows = Cells(Rows.Count, firstRng.Column).End(xlUp).Row

entireRange.Copy Destination:= firstRng.Cells(lMaxRows + 1)

Or

entireRange.Copy Destination:= Cells(lMaxRows + 1, firstRng.Column)

Upvotes: 2

Related Questions