PDA

View Full Version : Solved: Make range dynamic?!



enfantter
10-16-2007, 12:38 AM
Hey all,

Im difining a range, like below
Set Rng2 = Sheets("Indtastning af ny opgave").range("b2:b60")

The problem is that the range is take from an input sheet in which it is very likely that there can be added extra records, so instead of b60 i would like to have a dynamic piece of code.
Something like b"lastrow"...
does anyone know how to do this?!

anandbohra
10-16-2007, 01:50 AM
some what like this


With ThisWorkbook.Worksheets("Indtastning af ny opgave")
Set Rng2 = Range(.Range("B2"), .Range("B65536").End(xlUp))
End With

mdmackillop
10-16-2007, 04:54 AM
Because Excel 2007 has more than 65536 rows, it's safer to use
Set Rng2 = Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))

which will work for all versions.

johnske
10-16-2007, 05:03 AM
orSet Rng2 = Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))