storing a column within another variable in VBA

Question

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"

When using firstRng use Cells and not Range and use firstRng.Column to return the column number: lMaxRows = Cells(Rows.Count, firstRng.Column).End(xlUp).Row And Cells(lMaxRows , firstRng).Select — Scott Craner
– Scott Craner, Commented Nov 17, 2021 at 15:00

BigBen · Accepted Answer · 2021-11-17 15:06:11Z

2

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)

answered Nov 17, 2021 at 15:06

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

storing a column within another variable in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related