Simple macro, yet trouble

[frederikbt]

Hello all

I want to make a very simple macro, but its causing me lots of trouble. Might be that my VBA-skills aren't updated

I have a long list of data in column A in Sheet1. I want to create a macro that copies data from cell B1 in Sheet2 to the first empty cell in column A in Sheet1.

I have no problem creating a macro that copies to the last cell with data, but I want the first EMPTY cell.

Can anyone help?

Thanks in advance

Fred

[jmcilhinney]

This is the VB.NET forum, for questions on VB.NET. VBA questions belong in the VBA forum. I have asked the mods to move this thread.

[gep13]

Moved. Thanks for the report jmcilhinney

Gary

[Hack]

We must have both moved this at the same time.

I have an additional question. You mention Sheet1 so it is presumed that you are using Excel VBA.

It is important for anyone wishing to assist you to know what version of Excel you are using.

[MarMan]

Another thing to note is what do you consider the first empty cell? Do you start in the first column and keep going down? Or start in the first row and keep going across? Or a more difficult square pattern? Rectangle? So you see, the first cell can be many different things to many different people and I presume only one way will work for you.

The first step is to CLEARLY and COMPLETELY define the problem AND solution.

[westconn1]

try

range("a65535").end(xlup).offset(1) = range("b1")

[unleash]

I think westconn way works best, but if you need a number instead of a address this works as well:

for i = 1 to 256 ' ' 256 is the maximum number of cols excel contains.
  If isEmpty(Cells(0,i)) then Exit For ' ' No endless loops for me, right
next i
Cells(0,i).paste ' ' This is for pasting, not sure if this works like this...

That will give you the column number. Swap the "0" and the "i" in the "isEmpty" statement to get the row number. Make sure you change the 256 to 65536 when doing rows.

[westconn1]

Swap the "0" and the "i" in the "isEmpty" statement to get the column number.

other way round, the example is doing columns

[unleash]

Sorry, my bad.

I changed the original post, so nobody gets confused