vba getting active cell range and column

[plost33]

I want to assign the active cell's range to a variable and the column to another variable. i have tryied the following with no success. can someone assist?

MasterCell = ActiveCell.Range
MasterColumn = ActiveCell.Column

 

[JackDanIce]

Hi run this and see if you can work it out:

Sub testme()

Dim irange As Range
Dim icol As Integer

Set irange = ActiveCell
icol = ActiveCell.Column

MsgBox irange.Address
MsgBox icol

End Sub

 

[Richard Schollar]

Hi

You need to assign the range to a range variable - you can then use this to do things with the range and/or access properties such as the column or row number:

Dim r As Range

Set r = Activecell   'note use of Set keyword as assigning an Object ref!

'do stuff:

MsgBox r.Column   'column of r

Range("A1") = r.Address   'write address of r to cell A1

Range("B1:B10").Value  = r.Value  'assign value in r to B1:B10