xlToRight Issue

[happyhungarian]

Hi, I'm using the following code to find the end of a range but the problem I'm having is that range (the xlToRight) can change. Is there a way to instead have this select all the way to the right until it runs into a certain value? For example, if I have the word "END" in column T it would do the xlToRight all the way until Column T regardless if there were values between Column F (the "6" in the Cell function) and Column T?

Range(Cells(LR, 6), Cells(LR, 6).End(xlToRight)).Select

 

[DanteAmor]

Try this

Range(Cells(lr, 6), Cells(lr, Columns.Count).End(xlToLeft)).Select

 

[happyhungarian]

Thanks... the only issue with this approach is I have another set of data adjacent to the first set so this code will now cross both sets of data rather than just the first... hope that makes sense!

 

[DanteAmor]

Try this

Set b = Rows(lr).Find("[COLOR=#ff0000]End[/COLOR]", LookIn:=xlValues, lookat:=xlWhole)
If Not b Is Nothing Then
    Range(Cells(lr, 6), Cells(lr, b.Column)).Select
Else
    Range(Cells(lr, 6), Cells(lr, 6)).Select
End If

 

[happyhungarian]

That worked! Youre awesome... thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.