Finding the next available cell

[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

 

[Andy Brown]

Range("A1").Copy Range("Sheet1!A65536").End(xlUp).Offset(1, 0)

Rgds,
Andy

 

[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

 

[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

 

[Chip Pearson]

Andy,

but what does Offset(1, 0) mean?

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?

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