VBA – Define dynamic ranges

In VBA code we can define a dynamic range by checking the last row that has data on it. this can be defined in various ways. Here is a simple way of doing it:

Dim rng As Range
rng = Range("A12:C" & Worksheets("Sheet1").Range("C65535").End(xlUp).Row + 1)

This will get us the range A12:C200 if C200 is the last used cell in column C.
You can also define a range that gets all of the columns in row 1 that are used as column headers, like this:

Dim ws As Worksheet
Dim rng As RangeSet
ws = ActiveSheetSet
rng = Intersect(ws.Rows(1), ws.UsedRange)

This will get us the range of A1:D4 if columns A, B, C and D have data on it.

5 comentários:

velraj said...

Feeling so good to read your information's in the blog. Good job!
DOT NET Training in Chennai
dot net classes in chennai
Dot Net Training in Adyar
Dot Net Training in OMR
Html5 Training in Chennai
html course fees in chennai
QTP Training in Chennai
LoadRunner Training in Chennai

Aparna said...

Well done post...! I really enjoy to read your innovative blog and surely I will share this blog with my friends. Please update more details about this topic...
Oracle Training in Chennai
best oracle training institute in chennai
Oracle DBA Training in Chennai
Corporate Training in Chennai
Embedded System Course Chennai
Unix Training in Chennai
Excel Training in Chennai
Linux Training in Chennai

Reshma said...

Such an excellent blog.I learned a lot here.share more updates
Angularjs training in chennai
Angularjs course in chennai
Spoken English Classes in Chennai
Software Testing Training in Chennai
PHP Training in chennai
Angularjs training in Thiruvanmiyur
Angularjs training in Adyar
Angularjs training in Tambaram
Angularjs training in chennai
Angularjs course in chennai

Riya Raj said...

Good blog!!! It is more impressive... thanks for sharing with us...
Selenium Training in Chennai
Selenium Training
selenium classes in chennai
Selenium Course in Chennai
Selenium Training in Annanagar
Selenium training in vadapalani
Digital Marketing Course in Chennai
Python Training in Chennai
Big data training in chennai
JAVA Training in Chennai

Rahuldevan said...

Thanks for sharing informative article with us..
QTP Training in Chennai
qtp course in chennai
best qtp training in chennai
QTP Training in Tambaram
QTP Training in OMR
LoadRunner Training in Chennai
Html5 Training in Chennai
clinical sas training in chennai
Spring Training in Chennai
Photoshop Classes in Chennai