Help to Understand VBA

[The doomed]

Morning. Ive posted on here for a good number of months now and some of the stuff you guys have helped with has been tremendous.

However I feel Ive not learned as much as I could and have possibly expolited the goodwill of many on here!

Example, yesterday I was looking for a hand with something and as ever I got the answer on here and even though its potentially quite basic I dont understand what the code is doing.

ActiveCell.EntireRow.Cut Worksheets("Closed Projects").Cells(Rows.Count, 1).End(xlUp).Offset(1)

Can anyone take the time to explain whats happening after the 'ActiveCell.EntireRow.Cut' which is quite obvious.

I'd like to use a copy of that code that inserts (not paste) the code between lines 4 & 5 - but I'd like to try and work it out myself.

And then, can you point me in the direction where I can learn this stuff rather than having to ask on here?

 

[VoG]

It would be easier to understand with a keyword

ActiveCell.EntireRow.Cut Destination:=Worksheets("Closed Projects").Cells(Rows.Count, 1).End(xlUp).Offset(1)

It is pasting to the first unused row in column A of the sheet Closed Projects.

 

[The doomed]

It would be easier to understand with a keyword

ActiveCell.EntireRow.Cut Destination:=Worksheets("Closed Projects").Cells(Rows.Count, 1).End(xlUp).Offset(1)

It is pasting to the first unused row in column A of the sheet Closed Projects.

Thanks. If you are able to, what do each of these parts mean inividually (ie how would I have known to write that if the guys here hadnt said so?):

Cells(Rows.Count, 1).End(xlUp).Offset(1)

Just trying to work out how to insert it between 4 &5 without someone explicitly telling me.

Cheers!

 

[VoG]

Sorry, I'll have to be explicit

Worksheets("Closed Projects").Rows(5).Insert
ActiveCell.EntireRow.Cut Destination:=Worksheets("Closed Projects").Range("A5")

 

[Domski]

Cells(Rows.Count, 1).End(xlUp).Offset(1)

If you still want to know what this means:

Rows.count - will return the total number of rows in a spreadsheet (different in 2003 vs 2007)

Cells(Rows.Count, 1) will therefore give you the last cell in column A on any worksheet.

Cells(Rows.Count, 1).End(xlUp) will then look up column A from the last cell to the first non-blank cell in the column (or cell A1 if they are all blank)

Cells(Rows.Count, 1).End(xlUp).Offset(1) then references the cell one row below that.

Dom

 

[The doomed]

Great! Thanks guys!

 

[Ivan F Moala]

Great! Thanks guys!

Also, try highlighting a Key word and Press F1 (Help) .... that coupled with what you read hear may help you understand further.