Set Range to C5:E5 xldown

[t33p33]

Trying to set a range. This would be based on a dynamic set of data. The data is in C5:E5 with a variable number of rows.

Can't see to get this one correct. Here is what I am currently using.

Set rng = Sheets("Total Hours by Manager").Range("C5:E5").Select
    Range(Selection, Selection.End(xlDown)).Select.SpecialCells (xlCellTypeVisible)

Later in the script I am using this range and need to ensure that I am picking up all of it.

Any assistance is appreciated.

 

[Luke M]

I think the Select in the middle of the 2nd line was messing you up. But, here's the whole thing, with the Set Rng as a single line

Dim Rng As Range
With Sheets("Total Hours by Manager")
    Set Rng = .Range("C5", .Range("E5").End(xlDown)).SpecialCells(xlCellTypeVisible)
End With

 

[t33p33]

Getting closer! Columns C and D are part of a Pivot table. Column E has a formula that calculates based upon the values in the same row in C and D. The spreadsheet contains many more rows in Column E, than in C or D. They are conditionally formatted to hide the text if there is nothing showing in C or D.

If were to highlight C5:E5 and then xlDown, it will only go to the bottoom of the pivot table rows C and D. If I use the above script it goes all the way to the last row of data in column E.

Is there a way to easily get this to function in the same way as I describe above and only go to the bottom of the last row of data in C and D (which will always be the same row), yet still set the associated values in E as part of the range?

 

[t33p33]

Never mind! If I use what you wrote above and use .Range("E5", .Range(C5") it works just fine!

Luke, Thank you for your help! You made my afternoon much more productive! Appreciate the response and assistance!

 

[Luke M]

Sweet, glad it worked!