End(xlDown) won't work

[mike]

I've been using the following code to copy a range of cells to another
worksheet, appending each time.

Sheets("Order").Select
Range("R2:AP2").Select
Selection.Copy

Sheets("data").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

It's worked fine until I've directed it to another sheet, "data". Now,
I get a run time erroe 1004, App defined or Object defined error.

The first blank row in column A starts with cell A2. That's where it
should start. In A1 I have the word "Description". I've tried numbers,
even left A1 blank and still get the same error. Cells to the right of
A1 have other headings. If I do a manual Paste it works.

Thoughts?

 

[mike]

I just discovered that if I put something in A2 it will start in A3. It
seems to have a problem with A2 for some reason, but not A3.

 

[Guest]

The End() function works on either the presence of data or the absence of
data. If your starting point contains data and the next cell, in your case
A2, is blank. It will stop because it believes it has reached the end of the
series. If you start in a blank cell and the next cell contains data, it
will stop for the same reason. It looks for all of the cells that are like
the one it starts in, either >"" or = "". Does this help?

 

[mike]

Well I'm wanting to start at the first 'different' cell, that is the
first empty cell.

Apparently something about there only being one non-empty cell in the
range specified is throwing it off.

For example, I'm trying to find the first unused cell to paste to. It
doesn't work until I have at least two cells in the range that are not
empty. If only one is not empty, it freaks. I don't know why. I can add
an extra row to get it to work but it's really bugging me as to why it
doesn't just work with only one nonempty cell.

 

[NickHK]

Mike,
Does it work with just
Sheets("Order").Range("R2:AP2").Copy
Sheets("data").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial
Paste:=xlValues

There is normally no to .select object tin order to work with them.

NickHK

 

[Guest]

When you say you directed it to another sheet, exactly what do you mean.
What changes did you make to the code you posted, or is the code you posted
the changed code?

 

[Guest]

Mike, your code works OK. Check it by stepping through one line at a time.
I think you might have part of a line of code below the line it should be
part of.

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, 'No attenuator here
SkipBlanks:= _
False, Transpose:=False

This should all be one line of code.

 

[mike]

Nick,

Thanks but it works exactly the same. Stalls at the paste line unless I
put something in A2, then it starts at A3 ok.

 

[mike]

JL,

The code got reformatted when I pasted to the forum.

I was copying to sheet"Build List" then I decided to copy to another
sheet. No other changes. So, I went into the code and just replaced
"Build List" with "data". That's all I did, promise...

The difference is in the number of nonempty cells before it gets to the
first empty cell. Apparently, it won't work if there's less than two
nonempty cells before the first empty cell. When sheet "data" is clean
and only the header row (row 1) is filled (with header titles in each
column), it stalls. When I go to row 2 (A2) and put something in it,
then it'll run fine, starting at cell A3.

??

 

[NickHK]

Mike,
Yes, but starting from A1, if there are no entries then what do you expect.
You are looking for the end of blank cells.

NickHK

 

[mike]

I'm actually looking for the end of nonblank cells (or the beginning of
blank cells).

That's how it was working before anyway.

 

[mike]

Still no luck. Anybody want to see the file?

I'm actually looking for the end of nonblank cells (or the beginning of
blank cells).

That's how it was working before anyway.

 

[Roger Govier]

Hi Mike

I can't explain why it works for you in one scenario rather than
another.
However, I always tend to use xlUp rather than xlDown when finding the
lastrow, just in case there are any blanks

Something like
Range("A" & (Cells(Rows.Count, 1).End(xlUp).Row) + 1).Select
should produce the correct result whether you just have data in the
header row, or any rows beneath.

The first 1 in the line refers to column 1.
The second 1 is merely adding 1 to the cell count, to take the selection
to the first line after that in which data was found.

I am sure others will have far more efficient methods, but this works
for me.