VBA Find LastRow Of Multiple Non Contiguous Columns On One SHeet

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to find the LastRow and apply formatting to two non contiguous rows with differing lengths columns, but on the same sheet.

Please Login or Register  to view this content.

The code works fine, but I'm aware it's a little cumbersome.

Could someone possibly tell me please, is there a way that these could be amalgamated.

I have searched for this, but all the examples I've found don't apply to 'non-contiguous' ranges or are unsuitable.

Many thanks and kind regards

Try this...

Please Login or Register  to view this content.

Cheers!!

Hi Andy, thank you once more, for taking the time to reply to one of my post and for the code.

I'd not even heard of the 'Union' function, but this is really very useful and works great.

All the best and kind regards

Chris

Hi @excelliot, thank you for taking the time to come back to me with this and the time in putting together the code which works very well!

All the best and kind regards

Chris

Cheers!!

Hi, I wonder whether someone may be able to help me please.

With some adaptation, I using the code kindly provided by Andy Pope to apply formatting and sorting to multiple ranges of differing lengths. The code I've put together is as below:

Please Login or Register  to view this content.

The problem I have is that the number format is not being applied to all the cells in column F even though it is not at the beginning or end of the ranges.

The difficulty I have in understanding this that column F is shorter than column Q for example, yet this column is corrected formatted.

I just wondered whether someone may be able to look at this please and let me know where I'm going wrong.

Many thanks and kind regards

Chris

Hi Andy, thank you for coming back to me with this, I really appreciate it.

I also thank you for the explanation.

I actually thought it was this piece of code which controlled the range "Range("Q10:R" & Cells(Rows.Count, "Q").End(xlUp).Row))" because of the 'Union' function and because all of the other columns in the whole range, with some columns shorter and longer than column B were correctly formatted, oh how I was wrong. I can confirm that the second script is what I was after and works great!

Once again many thanks for your help and kind regards

Chris