Finding the next available cell

H

hce

Hi

Say in Sheet1, there's data from row1 to row200. Is it possible t
write a code that searches Sheet1 and identify Row 201 as the nex
available cell hence pasting the new data starting from there...?

Cheer
 
C

Chip Pearson

Try something like

Dim NextOpenCell As Range
Set NextOpenCell = Cells(Rows.Count,"A").End(xlUp)(2,1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
H

hce

Hi Andy

Thank you very much for your help but what does Offset(1, 0) mean?

Cheers

Hi Chip

Thank you very much for your help but what does (2,1) mean?

Cheers

Obviously, I think both methods will do the same but how come the
numbers used are different?

Cheers
 
C

Chip Pearson

Andy,
but what does Offset(1, 0) mean?
Click to expand...

The Offset property returns a cell reference that is some number
of cells away from the original cell. The original cell is
Offset(0,0), so Offset(1,0) is 1 row down and 0 columns to the
right of the original cell.
but what does (2,1) mean?
Click to expand...

This is an alternative notation to Offset, but uses the _Default
property. Like Offset, it returns a reference to a cell that is
some number of cells away from the original cell. Unlike Offset,
the original cell is (1,1), not (0,0), so (2,1) is 1 row down and
0 columns to the right of the original cell.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top