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.

13 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

rinjuesther said...

awesome article,the content has very informative ideas, waiting for the next update...
clinical sas training in chennai
clinical sas training fees
clinical sas training in vadapalani
clinical sas training in Guindy
clinical sas training in Thiruvanmiyur
SAS Training in Chennai
Spring Training in Chennai
LoadRunner Training in Chennai
QTP Training in Chennai
javascript training in chennai

saran said...

such a awesome info bro,thanks for this post.if your looking for selenium courses or big data courses you can check out links below
.Very well written article thanks for posting this amazing article with us keep growing and keep hustling
Selenium course in chennai
best selenium training institute in chennai
best selenium training in chennai
selenium training in chennai omr
selenium training in omr
big data course in chennai
big data hadoop training in chennai
big data analytics courses in chennai

Rajesh said...

Nice infromation
Selenium Training In Chennai
Selenium course in chennai
Selenium Training
Selenium Training institute In Chennai
Best Selenium Training in chennai
Selenium Training In Chennai

Rajesh said...

Rpa Training in Chennai
Rpa Course in Chennai
Rpa training institute in Chennai
Best Rpa Course in Chennai
uipath Training in Chennai
Blue prism training in Chennai

Data Science Training In Chennai
Data Science Course In Chennai
Data Science Training institute In Chennai
Best Data Science Training In Chennai

Rajesh said...

Python Training In Chennai
Python course In Chennai
Protractor Training in Chennai
jmeter training in chennai
Loadrunner training in chennai

amit tavva said...

keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
digital marketing training in bangalore | https://www.excelr.com/digital-marketing-training-in-bangalore

lokesh said...

Thanks for taking time to share this useful blog...
spoken english classes in bangalore
Spoken English Classes in Chennai
spoken english class in coimbatore
spoken english class in madurai
Spoken English Classes in BTM
Spoken English Classes in Marathahalli
Spoken English Classes near Marathahalli
Spoken English Marathahalli
DevOps Training in Bangalore
DOT NET Training in Bangalore

sasi said...

I really enjoyed this article. I need more information to learn so kindly update it.
Salesforce Training in Chennai
salesforce training in bangalore
Salesforce Course in bangalore
best salesforce training in bangalore
salesforce institute in bangalore
salesforce developer training in bangalore
Big Data Course in Coimbatore
Python Training in Bangalore
salesforce training in marathahalli
salesforce institutes in marathahalli