asked on

Excel VBA - Locating the next available blank cell

I have a contiguous group of data in cells a1:F50 and would like to paste a group of cells at the first empty cell beneath this area, at location cell A51. Next month however, my first area may be different. It may be A1: F93 and so, I would like to have my cursor paste data in cell A94. the data I'm pasting is also variable in row length but not in column width.
I need coding help in positioning my cursor in the first blank cell underneath the data.
If I were doing this without VBA, I would use a combination of the END key and the UP and DOWN arrow keys.
1. Is this the best way in Excel VBA?
2. How can I do this in VBA?

Norie

There are various ways to find the next empty row.

Here's one.

Set rngNext = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)

Range("G1:G10").Copy rngNext

Open in new window

Something similar, without the Offset, can be used to find the last row of the data you are copying.

brothertruffle880

ASKER

I may not have been clear enough in my request:

This is how I perform the action using the keyboard; I'd like to know the VBA code to do the following

I'm somewhere in an island of cells containing data.
I press the END button
I press the DOWN ARROW (I am now in the last cell in the island of data)
I press the DOWN ARROW again. (I am now in the first empty cell beneath the island of data).

How can I perform the above using VBA code.

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

ssaqibh

What if the active cell is in row 10 of the 'island', there's blank row in row 18 and the last row is actually row 30.

Using ActiveCell.End(xlDown).Offset(1).Select you would end up with row 18 selected.

Saqib Husain

Hi imnorie,

In that case the block after row 18 is a new island. The OP is looking for the end of the current island. In other words he is looking for code which does "END-DOWN" which he has clearly spelled out in his followup.

regards, Saqib

Norie

Saqib

Perhaps you are right.:)