CLICK HR to check out m rand new xcel Add-in!

#FreeTrial ×

HOME ABOUT BLOG TUTORIALS CODE VAULT

TOOLS

5 Wa to Create A

Dnamic Auto-

Search
Adjuting VA Range
May 25, 2015 Chris Newman

My Favorite Add-ins & Articles!

Dynamic Code Is Vital!

A huge turning point for me when I was teaching myself

how to write VBA was being able to create dynamic code.
This meant I didn't have to go into my macros and
change range addresses in my code every time the size
of my data changed (which was typically every day).
Through lots of trial and error, I've come up with a list
of 5 different methods you can use to turn your static
range references into powerful, auto-adjusting machines!
All of these code snippets have advantages and
disadvantages, so make sure you pick the one that works
for your particular situation.
Method 1: UsedRange

The UsedRange method creates a range that includes

every cell that contains data in it on the spreadsheet. It is
vital that you refresh (recalculate) the UsedRange before
using it as the method will pick up ghost cells (cells that
had values in them and you used the Delete key to
remove the values). Manually you can do this by saving
your spreadsheet or you can use the command shown in
the below code to refresh with VBA.

Sub DynamicRange()
'Best used when only your target data is on
the worksheet
Latest VBA Code Vault Posts
'Refresh UsedRange (get rid of "Ghost" cells)
Worksheets("Sheet1").UsedRange
VBA Code To Loop Through Only
'Select UsedRange
Worksheets("Sheet1").UsedRange.Select Selected Excel Charts
4 WEEKS AGO
End Sub

VBA Code To Find And Replace

Words Within Specific File Names In
Method 2: Ctrl + Shift + Right Arrow/Down A Folder
Arrow A MONTH AGO

This code simulates the range that would show up if you Microsoft Word VBA To Save
were to use the keyboard shortcut Ctrl + Shift + Right Document As A PDF In Same Folder
Arrow and then Ctrl + Shift + Down Arrow. If the last cell A MONTH AGO

in the first row or the last cell in the first column are
empty, this code will not calculate properly.
Free Webinars

Sub DynamicRange()
'Best used when first column has value on
last row and first row has a value in the
last column

Dim sht As Worksheet

Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Sheet1")

Set StartCell = Range("D9")

'Find Last Row and Column

LastRow = sht.Cells(sht.Rows.Count,
StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row,
sht.Columns.Count).End(xlToLeft).Column

'Select Range
sht.Range(StartCell, sht.Cells(LastRow,
LastColumn)).Select
 End Sub

Method 3: SpecialCells - LastCell

This code uses the SpecialCells method (the non-VBA

terminology is called Go To Cells) to search for the last
cell containing data on the spreadsheet. Once this cell is
found, the code can use the cell reference of the "last
cell" to determine the last row and column.

Sub DynamicRange()
'Best used when you want to include all data
stored on the spreadsheet

Dim sht As Worksheet

Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Sheet1")

Set StartCell = Range("D9")

'Refresh UsedRange
Worksheets("Sheet1").UsedRange

'Find Last Row and Column

LastRow =
StartCell.SpecialCells(xlCellTypeLastCell).Row
LastColumn =
StartCell.SpecialCells(xlCellTypeLastCell).Column Most Popular Blog Posts
'Select Range
sht.Range(StartCell, sht.Cells(LastRow,
LastColumn)).Select Easy Step-By-Step Instructions To
Create Your First Excel Ribbon Add-in
End Sub
A YEAR AGO

The VBA Guide To Excel Pivot Tables

Method 4: CurrentRegion 2 YEARS AGO

This example uses the CurrentRegion method. 5 Different Ways to Find The Last
CurrentRegion tries to calculate the range associated Row or Last Column Using VBA
with a cell reference by analyzing the surrounding cells. If 2 YEARS AGO

there is a completely blank row or blank column,

CurrentRegion stops looking and assumes that you were Copy & Paste Multiple Excel Ranges
only wanting the data that was connected with your To Separate PowerPoint Slides With
starting point. Make sure there are no chances of your VBA
data having a completely blank row or column before 2 YEARS AGO

using this method.

Sub DynamicRange()
'Best used when your data does not have any
 entirely blank rows or columns

Dim sht As Worksheet Follow @ChrisMacro

Dim StartCell As Range

Set sht = Worksheets("Sheet1")

Set StartCell = Range("D9") The Spreadsh…
'Select Range 1.5K likes
StartCell.CurrentRegion.Select

End Sub
Like Page

Be the first of your friends to like this

Method 5: Static Columns

I've experienced lots of situations where the column

length never changed and the VBA code just needed to
dynamically adjust for row length. In cases like this, you
may not want to write lines to look for the last column.
The below code shows you how to adjust a ranges row
length dynamically.

Note: You could also use the LastRow calculation used in

Method 2 instead of the Find calculation shown below.

     
Sub DynamicRange()
'Best used when column length is static

Dim sht As Worksheet

Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Sheet1")

Set StartCell = Range("D9")

'Refresh UsedRange
Worksheets("Sheet1").UsedRange

'Find Last Row

LastRow = sht.Cells.Find("*",
SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row

'Select Range
sht.Range("D9:M" & LastRow).Select

End Sub

What Can I Do With A Dynamic

Range?

There are a ton of scenarios that may require you to have

an automatically expanding and collapsing range
reference. Examples could be:

Resizing a Pivot Table source range

Looping through cells in a data set
Deleting only the raw data range

There are many, many more examples of this and I'm am

sure you can think of a few examples yourself. Let me
know in the comments section below how you use
resizing a range in your macro code! Also, if you can
think of any other ways to use VBA code to resize your
ranges automatically, post your coding method in the
comments section so we can improve the current list. I
look forward to reading about your experiences.

How Do I Modify This To Fit My

Specific Needs?

Chances are this post did not give you the exact answer
you were looking for. We all have different situations and
it's impossible to account for every particular need one
might have. That's why I want to share with you: My
Guide to Getting the Solution to your Problems FAST! In
this article, I explain the best strategies I have come up

     
with over the years to getting quick answers to complex
problems in Excel, PowerPoint, VBA, you name it!

I highly recommend that you check this guide out before

asking me or anyone else in the comments section to
solve your specific problem. I can guarantee 9 times out
of 10, one of my strategies will get you the answer(s) you
are needing faster than it will take me to get back to you
with a possible solution. I try my best to help everyone
out, but sometimes I don't have time to fit everyone's
questions in (there never seem to be quite enough hours
in the day!).

I wish you the best of luck and I hope this tutorial gets
you heading in the right direction!

Chris "Macro" Newman :)

 43 Comments 5 Likes Share

 Excel, VBA
 Dynamic Range
  THE VBA GUIDE TO SENDING
HOW TOEXCEL
INSTALL
... AND UNINSTALL A VBA ...

Comments Community 
1 Login

Sort by Best
 Recommend 2 ⤤ Share

Join the discussion…

Ejaz
9 months ago
− ⚑

Two words CurrentRegion property! Or is it three?

That's what i use in VBA.
1△ ▽ Reply

Guy
4 months ago
− ⚑

Hi everybody!
I am trying to import data from one workbook to
another using the code below, but it returns the

     
message "Run-time error `1004`: Application-defined
or object-defined error".
The number of columns is always the same and the
rows can vary.

Sub DATA()

Dim Target_Workbook As Workbook

Dim Source_Workbook As Workbook
Dim LastRow As Long
Dim StartCell As Range
Dim LastCell As Range

Target_Path = "C:\database.xls"
Set Target_Workbook =
Workbooks.Open(Target_Path)
see more

△ ▽ Reply

sudha
4 months ago
− ⚑

HI,
I am making a dash board for recruitment of my
company.I need help.I have 12 sheets namely april ,
may ,june...till march , in each sheet there are several
columns such as name email id contact etc and final
columns such as name email id contact etc and final
feedback.
i want a macro or something that dynamically adds
few fields like name, experience , email id of only
selected candidates to a new work book so that other
values like offer date etc can be filled manually.
I will be glad if someone could help me for the same.
△ ▽ Reply

Ramesh
5 months ago
− ⚑

Sub Dynamic_Range()
Dim i, j As Integer
Worksheets("Faces").Select
For i = 1 To Rows.Count
For j = 1 To Columns.Count
If Cells(i, j).Value <> "" Then
Cells(i, j).Select
Exit For
End If
Next j
If Selection.Value <> "" Then
Exit For
End If
Next i

     
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection.End(xlToRight),
Selection.End(xlDown)).Select
end sub
△ ▽ Reply

Mikey1969
7 months ago
− ⚑

This is awesome... This may be what I need for what

I'm doing, but I've used one of these in the past with
no tips like this on how they work.

I'm curious if this is what I need...

I'm building a document that handles work tickets,
and when done will fill a form with all of the data, with
the number of rows based on how many parts were
used. I have a section on the work ticket that
organizes the parts used, and the 'Order Number'
field is the one that is always filled regardless of
which parts were used. Is something like this what I
would need? 3 parts would be 3 rows on the final
form, with most of the rest of the data being the
same... If this isn't the direction I need, I'll look
elsewhere.
△ ▽ Reply
△ ▽ Reply

Kishorekumar
− ⚑
8 months ago

Very useful post through..Thanks :) . I liked your

EXCEL Ribbon addin tutorial too.

Perhaps one of you could help me with my hunt..

Before I begin, I should tell you all that I am a

complete noob to excel and VBA macro.
I am trying to compare two excel sheet data by using
EXACT formula. sometimes the data ranges to 50
columns and 10000+ rows, So I am using a VBA
macro to define the range and applying the formula.
perhaps the following code will explain better, As i
feel you guys are more familiar with the code than
the language. :P

NOTE: Till now I was manually inputting the range

values to insert formula and apply the formatting. But
today seeing this post made me little greedy. :)

Sub FormulaInsertion()
see more

△ ▽ Reply

     −
Alejandro
8 months ago

I barely know how to use VBA but as formula if you

⚑

type cell(“Contents”) you get the last typed data, but

what if I just want to know the last typed data in a
range as a function?
△ ▽ Reply

deepak gaba
− ⚑
8 months ago

Hi Chris, I have a different problem, I need to find last

active column cell in each row and then use that.

I have a worksheet which includes the information of

various shipments, at various stages.

Below is the example of the sheet

Delivery note number date pick created y/n pick

issued to pick status packing status shipment
number PAPERWORK RECEIVED collection status
location of shipment POD

1 30/01/2016 Y XX CONFIRMED PACKING

COMPLETED 1234 YES ARRANGED BAY 1 34564
2 30/01/2016 Y XX CONFIRMED PACKING
COMPLETED 3456 NO WAITING

3 30/01/2016 Y ABC CONFIRMED

see more

△ ▽ Reply

Rob
− ⚑
8 months ago

This is really good stuff. Thanks for taking the time to

share with all of us noobies!
△ ▽ Reply

Chris > Rob

− ⚑
8 months ago
Thanks Rob!
△ ▽ Reply

cathig33
9 months ago
− ⚑

This is incredibly helpful! Can I suggest adding a 6th

way? I need to be able to select a subarea of all of
the cells that have data on my sheet. I know my
starting cell and have two cells updating with a count

     
of how many rows and columns I need in the range.
I'm using COUNTIF formulas for that.

I know using tables would pretty much solve my

issue, and I bet there's a way to do that (because if
you look long enough, you can do anything with
Excel), but I'm not using tables because I need two
levels of column headers that are dynamically
generated with formulas. So I have more rows and
columns with formulas in them than actually go in my
report.

The problem I want to solve is to set the print area.

The closest answer I have found is here: http://excel-
tips.blogspot.com... -- to use an OFFSET formula to
see more

△ ▽ Reply

Chris > cathig33

8 months ago
− ⚑

Have you tried recording a macro to see how the

code might be written?
△ ▽ Reply

cathig33 > Chris

8 months ago
− ⚑
 8 months ago

Interesting question ...Would I be recording

myself writing a formula in the Print_Area
formula field? I can try it on Monday when I
have access to Excel again.
△ ▽ Reply

Chris > cathig33

8 months ago
− ⚑

I hoping it would work. Sometimes the recorder

doesn't "record" certain actions within excel, but
it's definitely worth a shot!
△ ▽ Reply

cathig33 > Chris

− ⚑
8 months ago

Sweet! It worked!

To set a dynamic print range when it is possible

to calculate the number of rows and columns
that should print:

1. In two "hidden" or otherwise non-printing

cells, calculate the number of rows and
columns that should print. The code in step 3
below expects the row count in cell A2 and the

     
column count in cell A3 on a worksheet called
Report.

2. Print Preview the worksheet that should print

at least once; and/or verify that Formulas >
Name Manager has a named region called
"Print_Area" for the worksheet you want to
print.

3. Add this to your VBA:

see more

1△ ▽ Reply

Chris > cathig33

− ⚑
8 months ago

YAY! Thanks for giving a great tutorial on how

you solved this. I'm glad it worked for you!!!
△ ▽ Reply

Ian Chapman
− ⚑
9 months ago
Probably a silly question Chris, but do you write vba
code for specific bespoke needs?
△ ▽ Reply
 Chris > Ian Chapman
− ⚑
9 months ago

I don't do any consults as I don't typically have

time. I would recommend using an Excel forum
like MrExcel.com to get fast answers to your
questions on specific coding needs. The people
on that forum are awesome and super smart!
△ ▽ Reply

Ian Chapman
− ⚑
9 months ago

Hi Chris, this is my first time trying to write macro so

maybe i am doing something wrong?
When i run your code i get a run time error 1004 ?
Can you maybe help with some advice where i am
going wrong please?
Many thanks
Ian
△ ▽ Reply

Chris > Ian Chapman

9 months ago
− ⚑

Ian, I need more detail. Which method are you

using? What line is the error occurring on?

     
△ ▽ Reply

Load more comments

ALSO ON THESPREADSHEETGURU

Insert & Delete Table VBA To Quickly Create

Rows With Worksheet A PDF Document From
Protection
2 comments • 8 months ago• Selected
5 commentsExcel
• 7 months ago•

AvatarAgni — I have created AvatarIkram Sial — Hi Chris I

a vba to create table want make a web
from the browser I have a
consolidated data in video type of
myBrand Excel A Fast Way To Clean &
Formatting Add-in Trim Cell Values With
6 comments • 9 months ago• VBA
1 Code
comment • 10 days ago•

AvatarJamil — Some users AvatarRakitha Ihalage —

who use colour Thanks for posting
formatting frequently this macro. The trim
and insertion of their one works perfectly
✉ Subscribe
d Add Disqus to your site Add Disqus Add
ὑ Privacy
     

Affiliates | About | Tools | Example Files

Guru Solutions, LLC | © 2015-2016 | ALL RIGHTS RESERVED

Excel, PowerPoint, Word, & the rest of the Office Suite are registered trademarks of the Microsoft Corporation

This site is not affiliated with Microsoft Corporation.