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

Oh shucks, I hadn't thought about cutting and pasting into the same worksheet and my poor test design did not show it happening , I'm getting careless in my old age, I do apologise.

In my defence , your honour, it can be difficult to follow code without seeing the data structure.

BTW you didn't answer as to why you have started calling me John.

Brian

ALso for Mods attention, please

Good morning Brian.

I can see that an apology is in order. I have no idea why I started calling you John, and can only quote your comment - "I'm getting careless in my old age, I do apologise".

I think perhaps that the change of name may have been triggered by the moderator's comment that he will 'ask Jon to invstigate', and that somehow insinuated it's way into my tired brain. Worrying, isn't it?

I acccept your plea that it can be difficult to follow code without seeing the data structure, and hereby pronounce you Not Guilty of the charges you brought against yourself. You are free to leave this place without a stain on your character.

I'll be returning to this project later today to sort out some minor formatting issues arising from the changes, and I'm quite excited about adjusting that latest bit of code. I'm not sure if it's a bit sad to be excited about something like that, but, well, you know how it is....

By the way, (for the Mods attention, mainly) I'm still having trouble responding to this thread. Although I'm logged in, when I click on 'Reply' I'm taken to the Login page, which then doesn't accept my login details. I seem to be able to follow a cicuitous route which includes clicking on 'Nomadscot' in the top right hand corner, going to my profile, clicking on 'see all previous posts' and then clicking on the thread.

Perhaps we will speak later, but in the meantime, have a nice weekend.

Edit: First attempt to post this reply failed. About to try again. I notice that when I go to the second page of the thread I'm no longer shown as Logged In, When I go back to the first page, I am 'Logged In'. Can't understand why it rejects my username and password though. And to be honest, I don't really know how I came here this time - too complex!

Re: ALso for Mods attention, please

There is thread on the login issues here

http://www.access-programmers.co.uk/forums/showthread.php?p=1344124#post1344124

we are not the only ones having trouble.

I have rejigged my test data and rerun your code, it worked fine, then did the change as I illustrated to cut both rows together fine again.

Have a great weekend

Brian

Hi Brian,

Hope you had a good weekend.

Pleased to say that the code is working fine. Exactly what I wanted.

I do have one problem though which I didn't foresee and for which you may have some ideas.

If the item to be deleted is the last one on the infamous list it won't delete, because it copies and pastes itself! LOL. I never thought of that.

There are up to 63 items on the list, so this doesn't apply to item 63, where the code for the adjacent button merely Clear(s)Contents, but if the list hasn't yet reached 63 items then the problem arises.

Any idea how I can change the code so that if the row after the item to be deleted is empty, then the macro merely clears the contents, rather than clearing contents and cutting and pasting the last row?

As far as my VBA knowledge is concerned this is an insoluble problem, but I'm hoping you know better.



N.

Ok I'm not sure that I fully understand what is going on, ie all the nuances of your data and code, but my immediate reaction is to ask if you can use
Delete xlshiftup
If not I will need to see your code or better a sample sheet with the code.
Are you working with entire rows or a subset of columns? Xlshiftup will work in both circumstances .

Brian

You know, Brian, I've been using Excel and VBE for perhaps 15 to 20 years now. During that time I like to think that I've built up a reasonable knowledge of both. Not like yours of course, but enough for my needs.

However due probably to a) working under time constraints and b) laziness, I have a considerable number of blind spots that, well, shouldn't be.

One of them is 'delete, shift cells left, shift cells up' etc. In all the years I've been working with Excel, I've never bothered to explore that simple thing. Always worked around it without taking the trouble to spend a few minutes exploring it.

I have always worried about the consequences to the rest of the spreadsheet of moving cells around like that, and just ploughed on recklessly with what I was doing.

I now see from your last post that the selected range will move, without moving cells outside the range. A simple thing, a common sense thing and one that could have saved a lot of time had I taken a few minutes out of a busy work schedule to find out.

What's the moral in this story? I guess it's not to put stuff on the back burner, and to take time when working with spreadsheets to fully understand what you're doing, and not to just rely on a working formula or piece of code to carry you through, wthout trying to see why it works and if there's a better way.

Time and pressure of work permitting, of course.

Thanks once again.

I've been there where pressure of work makes you use what you know works rather than have the time or energy to explore better/different options, but I fell in love with IT back in the 60s and when I moved to PCs in semi retirement and could then work at home I used to try things in my spare time, which is why i still dabble , but I guess not everybody is a nut case.



Glad to have helped

Brian

Glad to have made your acquaintance Brian.

Thanks again.