function "Range" code syntax problem 1

[mscallisto]

Dim usedrowsCol_T as integer
usedrowsCol_T = Cells(Cells.Rows.Count, "T").End(xlUp).Row
Set Company = Worksheets("calls").Range("S2:T25")

In the above code I want to change the hardcoded T25 to say T and the last row used in col T

Set Company = Worksheets("calls").Range(" 'S2:T' & string(usedrowsCol_T)")

My syntax needs help

 

[SkipVought]

Hi,

Dim usedrowsCol_T as integer
usedrowsCol_T = Cells(Cells.Rows.Count, "T").End(xlUp).Row
Set Company = Worksheets("calls").Range("S2:T" & usedrowsCol_T )

[tt][highlight blue][white]
^{~ ~}[/white][/highlight][highlight red][white] To be ^~[/white][/highlight][highlight blue][white]
^~ BE ^~[/white][/highlight][highlight white][blue] safe on the 4^{th ~}[/blue][/highlight][highlight blue][white]
^~ ADVISED ^~[/white][/highlight][highlight red][white] Don't ^~[/white][/highlight][highlight blue][white]
^{~ ~}[/white][/highlight][highlight white][blue] get a ^~[/blue][/highlight]
[highlight red][white] 5^th on the 3^{rd ~}[/white][/highlight]
[highlight white][blue] Or you might not ^~[/blue][/highlight]
[highlight red][white] Come 4^th on the 5^th 4^thwith ^~[/white][/highlight]


[/tt]

Skip,

_[red][/red]

 

[PHV]

Perhaps this ?
Set Company = Worksheets("calls").Range("S2:T" & usedrowsCol_T)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

Hmm, are all the ranges on that sheet?

..

With Worksheets("calls")
    Set Company = .Range("S2:T" & .Cells(.cells.rows.count, "T").end(xlup).row))
End With

-----------
Regards,
Zack Barresse

 

[mscallisto]

Thanks Skip

"Logic is easy Syntax is @^%$#!!!".....I swear I tried that.


and thanks Zack, I usually like less lines of code but in this case I have many columns that I need the row count for so I grouped the row count logic up front for documentation.

 

[SkipVought]

Zack had a very valid point.

You will run into problems if the activeSheet is NOT calls.

Skip,

_[red][/red]