Last used column letter

[VegasNath]

I'm a little braindead this morning....

How can I get to the last used column letter in row 6?

[HansV]

Using a formula in a cell or using VBA?

[VegasNath]

Sorry, using vba.

My end result will be:

Range("C22:X22").Formula = "=SUM(C11:C20)"

X being the last used column in this instance.

[HansV]

Here is a function you can use:

Code: Select all

Function LastCol(RowNum As Long, Optional SheetName As String) As String
  Dim rngCell As Range
  Dim strAddress As String
  Dim intPos As Integer
  If SheetName = "" Then
    SheetName = ActiveSheet.Name
  End If
  With Worksheets(SheetName)
    Set rngCell = .Cells(RowNum, .Columns.Count).End(xlToLeft)
  End With
  strAddress = rngCell.Address
  intPos = InStr(2, strAddress, "$")
  LastCol = Mid(strAddress, 2, intPos - 2)
End Function

Use like this:

Dim strCol As String
strCol = LastCol(6)

or

Dim strCol As String
strCol = LastCol(6, "MySheet")

[VegasNath]

Thanks Hans. I was sure that I had used a simpler method previously that I just found.

iCol = Mid(Cells(6, 256).End(xlToLeft).Columns.Address, 2, 1)

This works up until Z, but I guess that I will run into trouble thereafter.

[VegasNath]

Code: Select all

If Mid(Cells(6, 256).End(xlToLeft).Columns.Address, 4, 1) <> "$" Then
    iCol = Mid(Cells(6, 256).End(xlToLeft).Columns.Address, 2, 1)
Else
    iCol = Mid(Cells(6, 256).End(xlToLeft).Columns.Address, 2, 2)
End If

....seems to work, unless I am missing something?

[HansV]

It wouldn't work if you had a lot of columns in Excel 2007 or later, where the columns go up to XFD.

[VegasNath]

Good point, Thanks.

[VegasNath]

Here is a function you can use:

Code: Select all

Function LastCol(RowNum As Long, Optional SheetName As String) As String
  Dim rngCell As Range
  Dim strAddress As String
  Dim intPos As Integer
  If SheetName = "" Then
    SheetName = ActiveSheet.Name
  End If
  With Worksheets(SheetName)
    Set rngCell = .Cells(RowNum, .Columns.Count).End(xlToLeft)
  End With
  strAddress = rngCell.Address
  intPos = InStr(2, strAddress, "$")
  LastCol = Mid(strAddress, 2, intPos - 2)
End Function

Use like this:

Dim strCol As String
strCol = LastCol(6)

or

Dim strCol As String
strCol = LastCol(6, "MySheet")

Hans, I have taken to using this function...

How can I find the last used column BEFORE Column AC?

[VegasNath]

This would be one of a few requirements, therefore would it be possible to pass AC as an argument?

[HansV]

Change the line

Set rngCell = .Cells(RowNum, .Columns.Count).End(xlToLeft)

to

Set rngCell = .Range("AC" & RowNum).End(xlToLeft)

[HansV]

You added a new request while I posted my previous reply. Here goes.

Code: Select all

Function LastCol(RowNum As Long, ColName As String, Optional SheetName As String) As String
  Dim rngCell As Range
  Dim strAddress As String
  Dim intPos As Integer
  If SheetName = "" Then
    SheetName = ActiveSheet.Name
  End If
  With Worksheets(SheetName)
    Set rngCell = .Range(ColName & RowNum).End(xlToLeft)
  End With
  strAddress = rngCell.Address
  intPos = InStr(2, strAddress, "$")
  LastCol = Mid(strAddress, 2, intPos - 2)
End Function

Use like this:

lngLast = LastCol(37, "AC")

[VegasNath]

Thankyou, works like a charm!

[VegasNath]

Hans, how can I get a column letter from another workbook? Something like....

strCol1 = LastCol(5, "AC", wb1.Sheets("Report"))

Also (separate question)
This returns the last used column letter. How can I generate the next column letter (first unused)

TIA

[HansV]

Here is a new and different version of the function:

Code: Select all

Function LastCol(RowNum As Long, ColName As String, _
    Optional Sheet As Worksheet, Optional Offs As Long) As String
  Dim rngCell As Range
  Dim strAddress As String
  Dim intPos As Integer
  If Sheet Is Nothing Then
    Set Sheet = ActiveSheet
  End If
  With Sheet
    Set rngCell = .Range(ColName & RowNum).End(xlToLeft).Offset(0, Offs)
  End With
  strAddress = rngCell.Address
  intPos = InStr(2, strAddress, "$")
  LastCol = Mid(strAddress, 2, intPos - 2)
End Function

Important: the third argument is no longer the name of a sheet, but a Worksheet object which can refer to a sheet in the active workbook or in another workbook. If you omit this argument, the active sheet in the active workbook is used.

The fourth argument is a column offset from the last used cell. If you omit it or specify 0, you get the column of the last used cell. To get the column of the next cell to the right, specify 1, and to get the column of the next cell to the left, specify -1.

Examples:

LastCol(5, "AC") returns the column name of the last used cell in row 5 to the left of column AC in the active sheet.
LastCol(5, "AC", , 1) returns the column name of the first unused cell in row 5 to the left of column AC in the active sheet.
LastCol(5, "AC", Worksheets)"Summary")) returns the column name of the last used cell in row 5 to the left of column AC in the Summary sheet in the active workbook.
LastCol(5, "AC", Workbooks("Invoices.xls").Worksheets("Summary"), 1) returns the column name of the first unused cell in row 5 to the left of column AC in the Summary sheet in the Invoices.xls workbook.

[VegasNath]

It must be said, you are an absolute DIAMOND!