VBA explanation

HI,

can someone explain me this code?

Please Login or Register  to view this content.

It finds the first blank cell in column A on a worksheet and pastes values to it.

What is pasted and what sheet it's pasted will be determined in the preceding lines of code.

/e My mistake I misread the OP.

Please Login or Register  to view this content.

The syntax for Cells() is cells(row number, column) so the above code references Cells(however many rows are in my sheet, column A)
".End(xlUp)" tells excel to move from the row number you earlier specified, upward, until it finds a cell with contents. it basically selects the last used cell in the specified column (column A)

Please Login or Register  to view this content.

This says "move one row down from Cells(however many rows are in my sheet, column A) and paste only the values from the range that was previously copied."

so basicly...if i have a blank space in column A, then copy the value from "where you reference is''

I don't know what that says. Regardless of your blank spaces in column A, the line of code you posted will undoubtedly find the last cell in Column A that has been used, and paste the information directly below it.

Not quite.

It doesn't find any blank cell in column A.

The code finds the first blank cell in column A and copies to there.

Not any blank space, but the bottom row. If you currently use 900 rows it will place it in 901. If you have 9000 rows it will place it in 9001.

If row 300 is blank but you have data in 301-500 it will place it in row 501.

Yes, what Solus said.

For that it might be easiest to loop through the column to find the first blank row.

No.

If you had data like that the code would find the cell under 22, not the blank cell.

To fill in blanks take a look at SpecialCells(xlCellTypeBlanks).

It won't be useful for filling in spaces between rows that currently have data.

Please Login or Register  to view this content.

XeRo

What are you pasting in that code?

Also where is Row defined?

extremis

It's not only used to fill the last cell with data.

You could be pasting a whole chunk of data.

For example you might be consolidating data on multiple sheets onto one sheet.

Using the code you posted you can append the data from each sheet below the data that's already there.

I'm pasting two white bunnies in a snow storm

That's what I thought.

Norie, if you're talking about ".Row", that just refers to the row's number. So he's all good. As for the pasting, maybe its two white bunnies in a snowstorm?

It would only paste if the clipboard was pre-loaded.

@ Laffy...

He was pointing out a typo. It should be rows.count. Rows the collection.

XeRo

I know, it pasted an entire Change sub into A2 when I ran it after changing Row to Rows.

Good thing it wasn't in the ws change event then :D

Oh, that makes sense. You must have edited it before I saw the problem. Yay for teamwork :D