find last column with data

[Legacy 287389]

Hi good people,

please help me with a formula that will look at range E2:BV500, and return the column address of the last column in this range which contains a value. So if column AB has a value in ANY cell, and no other columns to the right of column BA has any values in them, I want "AB".

all and any help will be greatly appreciated!. Thank you all.

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Column:	G	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6
2​
3​
4​
5​
6​
7​
8​
9​
10​							x
11​
12​
13​
14​
15​
16​

Sheet: Sheet24

Array formula in cell B1:
=SUBSTITUTE(ADDRESS(1,MAX(IF(C2:H16<>"",COLUMN(C2:H16),"")),4),"1","")

How to enter an array formula
1. Copy formula
2. Double click on cell B1
3. Paste formula
4. Press and hold CTRL + SHIFT
5. Press Enter

 

[nemmi69]

Try

Option Explicit

Dim ColLoop As Long
Dim RowLoop As Long
Dim ColEmpty As Boolean
Dim ColEmptyID As Long


Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String


    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function


Sub FindEmptyCol()
' E2:BV500


ColEmpty = True
ColEmptyID = 0


For ColLoop = 5 To 74
    ColEmpty = True
    For RowLoop = 2 To 500
        If IsEmpty(Range(Cells(RowLoop, ColLoop).Address).Value) = False Then
            ColEmpty = False
            Exit For
        End If
    Next RowLoop
    If ColEmpty = True Then
        ColEmptyID = ColLoop
        Exit For
    End If
Next ColLoop
MsgBox "First empty column = " & ColumnLetter(ColEmptyID)


End Sub

 

[Legacy 287389]

Thanx Oscar Cronquist, I do appreciate!!

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	Column:	G	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​							x
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​

<tbody>
</tbody>

Sheet: Sheet24

<tbody>
</tbody>

Array formula in cell B1:
=SUBSTITUTE(ADDRESS(1,MAX(IF(C2:H16<>"",COLUMN(C2:H16),"")),4),"1","")

How to enter an array formula
1. Copy formula
2. Double click on cell B1
3. Paste formula
4. Press and hold CTRL + SHIFT
5. Press Enter

 

[Legacy 287389]

nemmi69,

Thank you for the code, I was looking for just a simple formula though, but thank you anyways..have a good day!!

 

[shaowu459]

=SUBSTITUTE(ADDRESS(1,SUM((E2:BV500<>"")*COLUMN(E2:BV500)),4),1,"")

Ctrl+shift+enter run the formula