CODEWITHSUNDEEP
vbaexcel
Tim Wilkinson
Tim Wilkinson

Reputation: 3801

Excel VBA set active cell as range

Using VBA I am selecting the first empty cell in a column using the following

Range("A3").End(xlDown).Offset(1, 0).Select

How can I set this currently active cell as a range for calling later in the macro. I tried,

Dim Target as Range
Range("A3").End(xlDown).Offset(1, 0).Select
Target = Cells(Application.ActiveCell)

My apologies if this is a basic question.

Upvotes: 0

Views: 8713

Answers (3)

Gene Skuratovsky
Gene Skuratovsky

Reputation: 581

Here it is:

Dim Target as Range
Set Target = Range("A3").End(xlDown).Offset(1, 0)

Upvotes: 0

barryleajo
barryleajo

Reputation: 1952

If you mean the first empty cell at the end of the column then this is the slight variation required. I would counsel against using the variable name Target because this is also a VBA keyword.

Dim rng As Range
Set rng = Cells(Rows.Count, "A").End(xlUp).offset(1,0)
MsgBox rng.Address

Upvotes: 3

Gareth
Gareth

Reputation: 5243

You don't need to select the cell first and as you're assigning an object, you need to use Set like so:

Dim Target As Range
Set Target = Range("A3").End(xlDown).Offset(1,0)

Upvotes: 1

Related Questions