how to make my macro work beyond column z - lastcolumn macro [duplicate]

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: stackoverflow.com/questions/12796973/… — Luuklag
– Luuklag, Commented Sep 5, 2017 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
– DanM, Commented Sep 5, 2017 at 14:36

Scott Craner · Accepted Answer · 2017-09-05 14:00:39Z

1

Use the Address function of Cells():

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

answered Sep 5, 2017 at 14:00

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DanM Over a year ago

thanks scott, that fixed the issue. elegant solution.

Scott Craner Over a year ago

@DanM after the question is 15 minutes old please consider marking as correct by clicking the check mark by the answer.

DanM Over a year ago

how would i change the function LastCol to return a column letter instead of a column number?

Scott Craner Over a year ago

@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.

DanM Over a year ago

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.

MiguelH · Accepted Answer · 2017-09-05 14:35:37Z

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

Collectives™ on Stack Overflow

how to make my macro work beyond column z - lastcolumn macro [duplicate]

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related