range row

[greg]

What is the best way to take a range and then change the row of it.
Say my range is Range("myRange") which might be C5
I can do a .address then try to parse out the row. and put in my new row.
such as 10
but that is a kludge.

it seems like you can use .cell(5) but you need to know the offset.

is there a way to directly change a ranges row?
thanks

 

[RyanH]

If I read correctly you want the row portion of a range returned, right?
Yes, you can do that. In your case this is how:

Sub RangeRow()

Dim myRangeRow As Long

myRangeRow = Sheets("Sheet1").Range("C5").Row

MsgBox "myRange is on Row " & myRangeRow

End Sub

Hope this helps!

 

[greg]

Actually
I would like to end up with a range. that is the same column. and a new
row.

 

[RyanH]

I'm not sure how you want the Range Row changed. If you have code that you
need help with I would suggest posting it or give a little better detail.

 

[greg]

Well, I Could do something like this:

Dim vTmp As Variant
Dim sAddress As String
Dim objRange As Range
Dim iNewRow As Integer

iNewRow = 50

sAddress = Excel.Range("myRange").Address
vTmp = Split(sAddress, "$")
Set objRange = Range(vTmp(1) & CStr(iNewRow))


But I am wondering if excel has something built in to Range to just change
the Row. To make this prettier.

thanks for any help