Last row starting from row A

[ammartino44]

Hello. I'd like to paste into A and THEN offset by 1 row. This code starts in A2 unfortunately. How could I modify it? Also, why do I have to write PasteSpecial xl PasteAll? Couldn't I just write Paste? Thanks.

Please Login or Register  to view this content.

[GeneralDisarray]

It's starting in A2 because you have Offset(1)

Generally, you will need to do that to not overwrite the value in a table's last row.

You could check to see if you are at the top of the sheet, or if the value is blank,

Please Login or Register  to view this content.

For the pasteSpecial versus paste, just try it both ways and see what happens. Generally, you want to clean your code of this kind of statement anyway.

If you are trying to copy over some values, there are better ways to do that.

[ammartino44]

Makes sense. Thanks!!

[ammartino44]

I tried this with my code but it doesn't seem to work. The first part copies into A1 but then the rest of the line items don't copy anything.

Please Login or Register  to view this content.

[GeneralDisarray]

Just as I suspected... there is no need for the .copy / .pasteSpecial funny business.

Here's what I suggest instead.

Please Login or Register  to view this content.

Barring any typos (I just typed that into the browser) that should do it. You can just equate the values and avoid the copy/paste stuff. It's a really good habit to get away from, it's something we all do at one point because the Macro recorder does it....

[MarvinP]

Hi ammartino,

It looks like you are starting to learn to program in VBA and Excel. Each of us develops a style of programming that we understand.

One of the first problems is to extract a cell using VBA. Here are a few ways to get the stuff out of B7.
Range("B7")
Cells(7, "B")
Read my favorite author's answer at: http://spreadsheetpage.com/index.php...your_vba_code/

I rarely use any "Offset" methods in my programming as it needs to know what cell the offset is from and this is one more level of confusion for me.

The next style concern is to use a For Next construct or a For Each construct.

I prefer the older For Next construct as I like to know each row and column I'm working with.

I also don't like to use variable names of j,k,l as a more meaningful name makes code much more readable.

See which of these two subs make more sense. What kind of coder do you want to be?

Please Login or Register  to view this content.

Look at http://www.excelfunctions.net/VBA-Co...sentation.html for a start to indentation and line breaks.

[ammartino44]

Hey General. It still doesn't work. At the third line it returns "subscript out of range". Not sure why.

[ammartino44]

Hi Marvin. I like your code examples, but the problem is that they copy to the same cell in sheet 2, whereas I would like them to copy into A1, A2, A3, etc.

[MarvinP]

OK - I missed that part of the problem. No problem reading my code...
How about this then.

Please Login or Register  to view this content.

[ammartino44]

Thanks Marvin. This still has the same issue of copying into A2, not A1 because you are adding 1 to the last row.

[ammartino44]

I thought this might work but it's not for some reason. On the second if statement it doesn't read the lastrow right.

Please Login or Register  to view this content.