how to make my macro work beyond column z - lastcolumn macro

Question

Function lastrow(colName As String)
Dim sht As Worksheet
Set sht = ThisWorkbook.ActiveSheet
lastrow = sht.Cells(sht.Rows.Count, colName).End(xlUp).Row
End Function
Function LastCol(rowName As Double)
Dim sht As Worksheet
Set sht = ThisWorkbook.ActiveSheet
LastCol = sht.Cells(rowName, sht.Columns.Count).End(xlToLeft).Column
End Function
Function lastcell()
Dim sht As Worksheet
Dim lastrow As Long, lastcolumn As Long
Dim lastletter As String
Set sht = ThisWorkbook.ActiveSheet
lastrow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
lastcolumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
lastletter = Chr(64 + lastcolumn)
lastcell = lastletter & lastrow
End Function

how can i get the lastletter line to work for columns AA, AAA, AAAA etc as my routine uses the chr hack but only works on sheets A-Z.

Why do you define 3 functions, and make the last function not take the result of the first two functions? To convert a column number into a letter see: https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter — Luuklag, Sep 05 '17 at 14:23
@Luuklag - because the first two functions return a value based on a column or a row as an argument. the third function uses .usedrange which looks at the entire sheet to find max row and columns. note that the third function does not use an argument. — DanM, Sep 05 '17 at 14:36

score 1 · Accepted Answer · answered Sep 05 '17 at 14:00

1

Use the Address function of Cells():

lastcell = sht.Cells(lastrow,lastcolumn).Address(0,0)

answered Sep 05 '17 at 14:00

Scott Craner

148,073
10
49
81

thanks scott, that fixed the issue. elegant solution. – DanM Sep 05 '17 at 14:09
@DanM after the question is 15 minutes old please consider marking as correct by clicking the check mark by the answer. – Scott Craner Sep 05 '17 at 14:11
how would i change the function LastCol to return a column letter instead of a column number? – DanM Sep 05 '17 at 14:12
@DanM why? in vba it is better to use Cells() with numbers than to convert to a letter that vba will then need to convert to a number. But you can parse the return of the Address to return the letter portion, but this is the slowest method to refer to a cell. – Scott Craner Sep 05 '17 at 14:15
now that i think about it, you are right. it should return a number and not a letter. how can i parse it though, not sure how to do that. – DanM Sep 05 '17 at 14:32

score 0 · Answer 2 · answered Sep 05 '17 at 14:35

I use functions to find to convert the numeric column to its alpha equivalent

Dim mycol as string

mycol = WColNm(Mycell.Column) ‘ mycell.column is numeric. Function returns 
integer

Public Function WColNm(ColNum) as string
    WColNm = Split(Cells(1, ColNum).Address, "$")(1)
End Function

Or column letters to numbers

Dim IntCol as integer

Intcol = WcolNum(“A”)

Public Function wcolNum(ColNm) as int
    wcolNum = Range(ColNm & 1).Column
End Function

how to make my macro work beyond column z - lastcolumn macro

2 Answers2