storing a column within another variable in VBA-CodePudding

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"

CodePudding user response：

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)

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