Last populated cell(s) in column(s) (1 Viewer)

[Nomadscot]

Good morning.

I wonder if someone can help me with this VBA problem please.

I need to 'Cut' a Range which starts from the last populated cell in column A to the last populated cell in Column T.

I then need to paste those 'Cut' cells elsewhere on the same sheet.

I can define the 'Paste' range, but how can I select the Range to be 'Cut'?

Most grateful for any help that can be provided.

 

[Brianwarnock]

Good morning.

I wonder if someone can help me with this VBA problem please.

I need to 'Cut' a Range which starts from the last populated cell in column A to the last populated cell in Column T.

I then need to paste those 'Cut' cells elsewhere on the same sheet.

I can define the 'Paste' range, but how can I select the Range to be 'Cut'?

Most grateful for any help that can be provided.

I had to hit quote to be able to reply!

In Excel VBA you would find the last used cell in a column eg A by

LastRowA = WS.Range("A" & Rows.Count).End(xlUp).Row

 

[Nomadscot]

I had to hit quote to be able to reply!

In Excel VBA you would find the last used cell in a column eg A by

LastRowA = WS.Range("A" & Rows.Count).End(xlUp).Row

Good Morning Brian,

Thanks so much for your prompt reply.

What I'm stumbling over is selecting the range.

It would be the code for this:

Range("Last populated cell in Column A:Last populated cell in column T").Select

Once again, I appreciate your input very much. Thank you.

 

[Brianwarnock]

Good Morning Brian,

Thanks so much for your prompt reply.

What I'm stumbling over is selecting the range.

It would be the code for this:

Range("Last populated cell in Column A:Last populated cell in column T").Select

Once again, I appreciate your input very much. Thank you.

I'm having trouble replying on this thread however you require

Range("A" & lastrowa, "T" & lastrowt)

 

[Nomadscot]

Hi Brian,

My apologies for not coming back to you sooner - just got home a short while ago.

Also thanks for your reply. I wonder what's causing the difficulty in replying. Could it be something I'm doing wrong?

I tested your code as noted below, but unfortunately it fails (Method range of Object Global failed), so I suspect I haven't fully understood and got the syntax wrong.

Can you help further?

Range("A" & lastrowa, "E" & lastrowe).Select
Selection.Copy

 

[Brianwarnock]

That code in the worksheet object works for me, if you are not in the code attached to the worksheet you will have to reference the worksheet.

I will remind you that I did originally say that I was talking about Excel VBA whereas this thread is in Visual Basic forum. I was trying to be helpful but maybe I have been misleading.

This simple test worked fine.

Sub a()
Dim lra As Long, lrf As Long

lra = Range("A" & Rows.Count).End(xlUp).Row
lrf = Range("F" & Rows.Count).End(xlUp).Row
Range("a" & lra, "F" & lrf).Select
Selection.Copy
Range("a30").PasteSpecial

End Sub


Brian

 

[Brianwarnock]

Just noticed that you used E not T, was that a deliberate change?

Brian

PS I am out all day tomorrow, walking actually , it's a hard life being retired, but will checkback in the evening.

 

[Nomadscot]

I was trying to be helpful but maybe I have been misleading.

This simple test worked fine.

Brian

No sir, not misleading at all - most helpful! It's my inexperience that's at fault here. That 'simple test' works fine for me too - thank you very much indeed.

In answer to your second reply, I merely shortened the range (from col T to col E) to test the code in a separate blank workbook, rather than testing it in the huge (to me) workbook that it will actually run in.

Brian, I have been scratching my head over this for some weeks now, so I cannot tell you how overjoyed and how grateful I am.

Thanks so much.

As a fellow retiree, I wish you a long, happy and VBA - enjoying retirement.

(It would be interesting for me to know how long it has taken you to acquire your encylopaedic knowledge of VBA!).

N.

 

[Brianwarnock]

In answer to your second reply, I merely shortened the range (from col T to col E) to test the code in a separate blank workbook, rather than testing it in the huge (to me) workbook that it will actually run in.

very sensible, too many people just throw data at their code and then cannot check things properly.

As to how long it took me to gain my small knowledge of VBA , well I started in the late 90s at the charity I worked for part time. I finally retired in 2006 but have dabbled on this site ever since and the knowledge is diminishing, but I cheat, I google.
Some 50 + years ago a wise school teacher said that there is knowledge and there is knowledge of the knowledge, if you have the latter you can look up the former.

Best wishes and hope the projectcomes together

Brian

 

[Nomadscot]

...there is knowledge and there is knowledge of the knowledge, if you have the latter you can look up the former.

Brian

Wise indeed.

I've also been Googling this issue extensively, without success. Until now.

I now have an issue with the 'Cut and Paste' element, which is not going as smoothly as I would have hoped due to the method for Cut & Paste being different to that for Copy and Paste. The problem being that the line I need to Cut and Paste is actually two rows which includes some cells that are merged vertically and some horizontally. (In this case, A37:Y38).

The routine fails at the Paste because of those merged cells, even though the destination is unmerged. (It works fine Cutting and Pasting manually).

You will see from the (extracted) code below that the macro is intended to delete a line from a list, and because blank line are not permitted in this list, replace it with the last line in the list. Then that 'Cut' line (last row) has to be reformatted as the 'Cut' removes the formatting as well as the contents.

You may wish to cast your eye over it and give me the benefit of your hard won knowledge, but I already feel that I've imposed on you too much!

Range("A37:Y38").Select
Selection.ClearContents
Selection.UnMerge
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With

Rem Start Brian's code

Dim lra As Long, lry As Long

lra = Range("A" & Rows.Count).End(xlUp).Row
lry = Range("Y" & Rows.Count).End(xlUp).Row

Range("A" & lra, "Y" & lry).Cut Range("A37:Y38")

Rem End Brian's code

How was your walk today? Weather was good?

 

[Brianwarnock]

I have no experience of handling merged cells as I soon stopped using them as they do give problems, normally using something like centre across selection achieves the desired result.

I am having trouble understanding your setup and your code. Is it possible to attach a cutdown version of what you have and are trying to achieve.

Walk was good, shorter than normal due to work on a canal path we wanted to take. My Thursday walks are with the U3A so are never more than 10 miles.

Brian

 

[Nomadscot]

Hi Brian,

'Only' 10 miles, eh?


Dim wStroll As 10mileWalk
For Each wStroll In Merseyside
wStroll = Delete Nomadscot
Next wStroll

I'm not a walker. Love the canals though. Had a narrowboat once, and spent a lot of time cruising around England, although I never got to your neck of the woods

 

[Nomadscot]

'Only' 10 miles, eh? Well done!

Workbook(Nomadscot).Delete
Retiredbook(Nomadscot).Select
Range("Home:Car").Select
On Error GoTo Nearest Pub



I do love the canals though. Had a narrowboat once, and spent many happy times cruising around England.

As to the code, I don't know how - or if - I can attach something to my posts, but I really don't want to bore you with it anyway. I'll try to work through it, but if I start banging my head against the wall and screaming VerBAl abuse at old ladies in the street perhaps you won't mind if I trouble you again?

 

[Nomadscot]

OOOOOpps! Sorry!

And my apologies to the Moderators for the double post. I thought I had lost the previous one, so re-did it, only to find it had gone (although unfinished).

Incidentally, Brian, I to am having trouble not only posting to this thread, but also in logging in to the forum. The LogIn appears to be rejecting my Username and Password, but I seem (so far) to get in by clicking on the links in the advisory email.

 

[Vassago]

This is strange. I'm replying to this thread right now without problem. I'm not sure why some are having issues and some aren't. Jon will have to investigate as it's out of my hands.

 

[Nomadscot]

Thank you very much for taking the trouble, Vassago. It keeps asking me to Log In,(edit: when I try to post) (and rejecting my attempts to do so) although I clearly am already logged in.

John - how on earth can it be that when I run that code in a clean workbook it does exactly as I want. It's a joy to behold. (Note that I have taken your advice and removed all merges, using, where appropriate, 'Centre across Selection', instead. (This has necessitated the repeat of the routine, as it's actually the last two rows I need to move, not the last one.)

This works fine:

Sub Sheet4_Rectangle2_Click()

Dim lra As Long, lry As Long

lra = Range("A" & Rows.Count).End(xlUp).Row
lry = Range("Y" & Rows.Count).End(xlUp).Row
Range("A" & lra, "Y" & lry).Cut Range("A12:Y12")

lra = Range("A" & Rows.Count).End(xlUp).Row
lry = Range("Y" & Rows.Count).End(xlUp).Row
Range("A" & lra, "Y" & lry).Cut Range("A11:Y11")

End Sub


When I include it in a routine in my project, it selects the whole range from A1 to Y40, and fails because the source and target are different sizes?

This doesn't work (for the reason explained above).

Sub Picture466_Click()

Application.ScreenUpdating = False
ActiveSheet.Unprotect 'Password:=""
Rem Start Brian's Code:

Dim lra As Long, lry As Long

lra = Range("A" & Rows.Count).End(xlUp).Row
lry = Range("Y" & Rows.Count).End(xlUp).Row
Range("A" & lra, "Y" & lry).Cut Range("A40:Y40")

lra = Range("A" & Rows.Count).End(xlUp).Row
lry = Range("Y" & Rows.Count).End(xlUp).Row
Range("A" & lra, "Y" & lry).Cut Range("A39:Y39")

Rem End Brian's code

ActiveSheet.Protect 'Password:=""
Application.ScreenUpdating = True

End Sub

Crazy!

 

[Nomadscot]

Got it. Done & dusted. Sorted. Yes.

Got it John!

As so often the case, a simple error on my part.

When I changed from merged cells, to 'centre across selection', I failed take into account that this had the consequence of making the last cell in column Y an empty cell. So when your code was looking for the last empty cell in column Y, it found it waaaaay up the column, not in the row, I expected.

Some formatting changes, a few judicious '-' placeholders, a wee bit of adjustment to some other code and it's now working fine.

So once again thank you for your code, and also for the 'centre across selection' suggestion, which combined brought weeks of headscratching to a conclusion.

Great work, John.

 

[Brianwarnock]

Hi
Great that you have it working, Merged Cells would give you the same problem as Centre across Selection in that the lastrow code would only find data in the left most cell.

But I don't understand what it is doing, if you know the range why do you have to find it?
The Cut code is Rangeexpression.Cut so what is the Range("A12:Y12") doing?

The Cut only causes an update to the result of the lastrow when a destination is given, I think, it does in 2013 when I just tried it. You would need to do a delete.

If all columns have the same number of rows just test 1 usually Column A and your code would then be

Sub Sheet4_Rectangle2_Click()

Dim lra As Long, lrg As Long

lra = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lra - 1, "G" & lra).Cut  (Worksheets(5).Range("A5"))
 ' this was the destination in my test
End Sub

BTW why have you decided to use my second name and how did you know it?

Brian

 

[Nomadscot]

A very good evening to you.

"But I don't understand what it is doing, if you know the range why do you have to find it?"

I know one range - the destination. I don't know the source range - the last (in fact two last) rows in the list.

I have a list to which lines are added at the bottom by VBA. The number of lines on the list is finite. Occasionally an item somewhere on the list has to be deleted. I have a button beside each line which will allow the user to do this as the Worksheet is protected and I don't want the user to have access to a heavily formatted page. (etc., etc)

However for security reasons the list must not have any blank spaces, and as the sheet is heavily formatted both for use and for printing, I can't just delete the row(s) (which also have data in hidden columns further to the right).

So my solution is to add some code to the button beside each line which after clearing the contents, will then take the last item on the list, cut it, and paste it in the newly created empty line.

Did I explain that clearly? And if you come back to me and say "Well why didn't you simply.....", then you could well be responsible for my final descent into madness.

"The Cut code is Range expression.Cut so what is the Range("A12:Y12") doing?".

At least in the Excel 2002 version that I'm using, the 'Range("A12:Y12") (as used in my test) is the destination when using the 'Cut' command. I quote:

"Cut Method

Cuts the object to the Clipboard or pastes it into a specified destination.
expression.Cut(Destination)

expression Required. An expression that returns an object in the Applies To list.
Destination Optional Variant. Used only with Range objects. The range where the object should be pasted. If this argument is omitted, the object is cut to the Clipboard."


So the Range("A12:Y12") would be changed to the appropriate range for each line deleted.

Did I explain that clearly enough?

 

[Nomadscot]

By the way, John, I note your code example. Thank you. I'm too tired to do anything about it tonight and I'm off to bed soon, but I shall be working on it tomorrow.

TVM

N.