-
Solved: Make range dynamic?!
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?!
-
some what like this
[VBA]With ThisWorkbook.Worksheets("Indtastning af ny opgave")
Set Rng2 = Range(.Range("B2"), .Range("B65536").End(xlUp))
End With[/VBA]
Always Mark your Thread as Solved the moment u got acceptable reply (located under Thread tools)
Practice this & save time of others in thinking for unsolved thread
-
Because Excel 2007 has more than 65536 rows, it's safer to use
[VBA] Set Rng2 = Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
[/VBA]
which will work for all versions.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
or[VBA]Set Rng2 = Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules