Find the last Cell with content in a given range.

In my worksheet all the cells in F6:F100 have formulas , either resolving to content or remaining blank.

I'm trying to find and select the last cell in F6:F100 with content , and make it bold.

Can someone suggest some VBA to satisfy this?


Grateful for any advice.

Take your pick and change to fit your needs

Please Login or Register  to view this content.

Thanks jolivanes. This gives a message box with the row number containing the last content. It doesn't go to the specific cell in the range and make it bold. Perhaps it can be modified to do this.

I thought that someone with over 600 posts would be able to do that himself.
How else are you ever going to learn. It's commonly known as trial and error.

Jolivanes - but I *am* working on modifying your suggestion. I said this in my response. Post #3.

Originally Posted by jolivanes

I thought that someone with over 600 posts would be able to do that himself.
How else are you ever going to learn. It's commonly known as trial and error.

This is unnecessarily snarky. I understand why you said this but this is not the tone we would like to see here.

Originally Posted by CDandVinyl

Thanks jolivanes. This gives a message box with the row number containing the last content. It doesn't go to the specific cell in the range and make it bold. Perhaps it can be modified to do this.

The code gives you the building blocks to finish your solution. It shows you how to find the last cell containing content. When you post in the VBA forum and ask a VBA question, it is helpful to give some idea of your level of VBA skills. We need to know if you are trying to get past a sticking point ("How do you find the last cell with content?") or whether you need more ("Please do this whole thing for me"). Also, the phrase "last cell with content" is not precise in Excel. It could mean "the last cell with a formula" or it could mean "the last cell with a formula that returns a non-null result." The code covers either case, so it is back to you to say which one you want.

Just for the sake of keeping this moving, here is the solution for looking for the last cell with a formula:

Please Login or Register  to view this content.

I have not tested this because I have not taken the time to create a test file from scratch. Consider attaching a file with sample data for testing purposes.

Here is also a solution with Conditional formatting:


Please try this Conditional formatting formula applied to $F$6:$F$100:

Formula:

Please Login or Register  to view this content.

If you are upgraded to to latest version of Excel 2016 or newer also this CF-rule is possible:

Formula:

Please Login or Register  to view this content.

Try
Code:

Please Login or Register  to view this content.

If I hurt peoples feelings, my apologies. However, nowhere in the response in Post #5 does it mention what had been tried and that is what I was waiting for.
I don't off hand remember which respected helper has his signature as
"Give some a fish and he eats for a day, teach him how to fish and he'll eat forever."
In my opinion, that fits the problem here,
No hard feelings though.

Thanks guys for your help and various solutions. I'm grateful for your support. Problem solved!

Thanks for the feedback and rep. . Glad to have helped.

Which solution did you choose?

Hi HansDouwe

I tried the CF solution manually , and it worked well. It successfully made bold the last entry in the range in F.

I then tried to make some VBA to have it run in a macro , and presently it makes *all* of the cells in F go bold. The code I'm using is below.

I need to work on it...

Please Login or Register  to view this content.

Sorry, I'm not very familiar with VBA. I expect Jindon, Kvsrinivasamurthy or Jolivanes can say more about this.

HansDouwe - Thanks for your help. Much appreciated

Hoi Hans.
As you must have read, I got berated for my Post #4 but I am still waiting for the OP to tell me which of the two suggestion from Post #2 fit his/her needs.
He/She mentioned that the message Box gave the cell address but no mention if it is the end of values or the last cell with formulae which can go beyond the data range.
Of course, I assume it is values because it does not make sense to have a potential empty looking cell formatted as bold.
There are multiple answers available to him/her but here was my idea.
It does not have the error checking that jindon rightfully suggested should be included.

Please Login or Register  to view this content.

Thanks Jindon