# Last Row and Dynamic Named Ranges in VBA

Contents

## Overview

This article will deal with the most efficient way to find the last row in a column.

Then I will take this a step further to show how we can create named ranges on the fly. This in essence makes the range dynamic.

Note: All of the code below I have regularly used in my projects and have proven successful over time.

## Article Key Elements

### Find the next empty cell in a range , last row in a column

Method1: Cells reference

Method 2: Range reference

### Create a named range in VBA that is dynamic

Method 1: Range reference

Method 2: Cells reference

Method 3: 2 variables

## Download the sample file

### last row range macros

## 1. Find the next empty cell in a range

*In this first example I am using the Cells reference and referring to column “C” This can be changed to 3 which of course is the column number for Column “:C”.*

Sub Last_Row_Cells()

Dim Addme As Range

Set Addme = Sheet1.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) 'or replace "C" with 3

Addme.Select

End Sub

*Next the same formula is changes but this time the Range reference is used.*

Sub Last_Row_Range()

Dim Addme As Range

Set Addme = Sheet1.Range("E" & Rows.Count).End(xlUp).Offset(1, 0)

Addme.Select

End Sub

### 2. Create a named range in VBA that is dynamic

Creating a named range on the fly is a very effective way of dealing with changing data. When new data is added then we would run this code to update the named range. It carries no overhead and is simple to implement.

Here are the 3 methods that I use.

### Method 1: Range reference

Sub Dyno_Range1()

Dim LastRow As Long

LastRow = Sheet1.Range("C" & Rows.Count).End(xlUp).Row

Sheet1.Range("C6:E" & LastRow).Name = "Data"

Range("Data").Select

End Sub

### Method 2: Cells reference

Sub Dyno_Range2()

Sheet1.Range("G6:J" & Cells(Rows.Count, "G").End(xlUp).Row).Name = "Analysis"

Range("Analysis").Select

End Sub

### Method 3: 2 variables

Sub Dyno_Range3()

Dim LastRow As Long

Dim LastCol As Long

LastRow = Sheet1.Cells(Rows.Count, 13).End(xlUp).Row

LastCol = Sheet1.Cells(6, Columns.Count).End(xlToLeft).Column

Sheet1.Range(Cells(LastRow, 13), Cells(6, LastCol)).Name = "MyRange"

Range("MyRange").Select

End Sub

Is sheet1…. a public variable or a typo? In the above examples i expect: sheet(1)…..

Hi Cmol,

Sheet1 is the code name for the sheet. You will see the code name in the project explorer to the left of the sheet name.

Trev

hello,

I have following code which I want to make to work whenever I select ExponentialDeclineA. The code works return a correct value but only works if my data is in the correct cells that code is looking for.

question:

in this "n = .Cells(.Rows.Count, "B").End(xlUp).Row" can I remove "B" and refer to x,y,w which I define when I use ExponentialDeclineA in excel?

Function ExponentialDeclineA(x, y, w As Range)

With ActiveSheet

n = .Cells(.Rows.Count, "B").End(xlUp).Row

End With

A = 0

B = 0

C = 0

D = 0

E = 0

F = 0

For i = 1 To n

If Cells(5 + i, 2) = "" Or Cells(5 + i, 3) = 0 Then GoTo Lastline Else GoTo Line1

Line1:

A = Cells(5 + i, 2) * Cells(5 + i, 2) * Cells(5 + i, 4) + A

B = Cells(5 + i, 4) * Log(Cells(5 + i, 3)) + B

C = Cells(5 + i, 2) * Cells(5 + i, 4) + C

D = Cells(5 + i, 2) * Cells(5 + i, 4) * Log(Cells(5 + i, 3)) + D

E = Cells(5 + i, 4) + E

F = Cells(5 + i, 2) * Cells(5 + i, 4) + F

GoTo Lastline:

Lastline:

Next i

ExponentialDeclineA = Exp((A * B – C * D) / (E * A – F * F))

End Function

thank you very much