# Finding the last cell in a range



## sharkei (Sep 1, 2005)

Hi

I export data from an accounting application into excel, and column A is a list of account codes. I know what row the first one will be in, but I don't know how many account codes there could be.

Does anyone know the VBA code to enable me to find the last row in the range? I will then be able to create named ranges for some of the other columns of data.

(Thiscell = "F" & lastrow
thiscol = 6
Thisrange = "=DATA!R3C" & thiscol & ":R" & lastrow & "C" & thiscol
ActiveWorkbook.Names.Add Name:="APTwo", RefersToR1C1:=Thisrange)

Thanks

I normally know what it is I want to do, but have always found 'finding out how to do things' in VBA difficult. The help screens don't help - me anyway! Is there a good reference book anyone can recommend, which is organised from a 'How to .....' perspective, rather than just giving a list of the commands and their syntax?

Thanks again.

Regards

Keith


----------



## Glaswegian (Sep 16, 2005)

Hi Keith

You can find the last used cell in a range by using this

```
Sheets("Sheet1").Range("A65536").End(xlUp)
```
or assign a variable to the last row like this

```
LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
```
and use Offset to find the first blank cell after the last cell with data

```
Sheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)
```
For specialist Excel help try

MrExcel.com or vbaexpress.com

They both have excellent boards covering all Excel topics.

Regards


----------



## sharkei (Sep 1, 2005)

Glaswegian

Thanks for your guidance.

Easy when you know how!

Kind regards

Keith


----------

