Range as variable

[Steph]

Hi. I have a variable defined as below:
var3 = Cells(ActiveCell.Row, "D").Value

How can I convert var3 to be (in laymans terms) ActiveCell.Row, "D" through
BB xlleft

So if active row is 3, var 3 might be D3:G3, if G was the last data point
and H and to the right was blank.

Thanks!

 

[Bernie Deitrick]

Steph,

Dim Var3 As Range
Dim i As Integer

Set Var3 = Range(Cells(ActiveCell.Row, "D"), _
Cells(ActiveCell.Row, "D").End(xlToRight))

For i = 1 To Var3.Cells.Count
MsgBox Var3(i).Value
Next i

HTH,
Bernie
MS Excel MVP

 

[Doug Glancy]

Steph,

Sub test()

Dim last_column As Long
Dim row_data As Range
Dim data_cell As Range
Dim var3

With ActiveSheet
last_column = .Range("BB" & ActiveCell.Row).End(xlToLeft).Column
Set row_data = .Range(.Cells(ActiveCell.Row, 4), .Cells(ActiveCell.Row,
last_column))
row_data.Select
For Each data_cell In row_data
var3 = data_cell.Value
Debug.Print var3 'for testing
Next data_cell
End With

End Sub

hth,

Doug Glancy

 

[Alan Beban]

Hi. I have a variable defined as below:
var3 = Cells(ActiveCell.Row, "D").Value

How can I convert var3 to be (in laymans terms) ActiveCell.Row, "D" through
BB xlleft

So if active row is 3, var 3 might be D3:G3, if G was the last data point
and H and to the right was blank.

Thanks!

addr = Cells(ActiveCell.row, "IV").End(xlToLeft).Column
Set var3 = Range(Cells(ActiveCell.row, "D"), Cells(ActiveCell.row,
addr))
MsgBox var3.Address

Alan Beban