Finding the last used Column

[Closed]
Report
-
 Rayh -
Hello,
Can anyone help. I want to find the last column used so that I can compare it with the first column


2 replies

Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
238
Hello Al,

See if this is what you are wanting:-

Sub FindLastColumn()

    Dim lCol As Long
    
        lCol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
        Columns(lCol).Select
        MsgBox "The last column used is...." & lCol
        
End Sub


Cheerio,
vcoolio.
Thanks for the reply, I should of said, I wanted to do it in WB not VBA
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

Thank you for that. Could you please give a little more info. How do I use 'FIND'?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768 > Al
I stand corrected. Find will not easily work for you. A VBA code is needed
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

Thanks anyway.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You can use CONTROL + END. It should stop at the intersection of max row used and max column used.
You can use this formula to return the last column number.
It is an array formula and must be entered using Ctrl-Shift-Enter to wrap it in { and }

This example returns the last column number in row 1.
=MAX((1:1<>"")*COLUMN(1:1))


This examples returns the last column number in row 10
=MAX((10:10<>"")*COLUMN(1:1))