how to find the address of last used column using vba

Question

Possible Duplicate:
Last not empty cell in row; Excel VBA
Finding the number of non-blank columns in an Excel sheet using VBA

Hello I have written a vba code for getting the address of selected cell(active cell). But I want the address of last used columns address,here is the code what i wrote

Dim a As String
a = Split(ActiveCell.Address, "$")(1)
MsgBox a

it is working correctly but i want the address of last used columns. like i have the values upto "AB" columns I want get that address using vba code.

@Jean-FrançoisCorbett: That link mentions getting the last column from a specific row or using the `UsedRange` property. I am sure that you are aware that `UsedRange` is highly unreliable? Remember this conversation? ;) http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920 — Siddharth Rout, Oct 03 '12 at 10:51
@SiddharthRout: Yup, but the point is, this question is a duplicate times a million. It should be closed as such. — Jean-François Corbett, Oct 03 '12 at 16:18
@Jean-FrançoisCorbett: I absolutely agree with you on that. It's just that the duplicate has to provide all the info the user actually asked. — Siddharth Rout, Oct 03 '12 at 16:20
@Jean-FrançoisCorbett: I just noticed Brettdj's link. Now that link has both the solutions. I am also casting my vote to close this thread :) — Siddharth Rout, Oct 03 '12 at 16:26

score 3 · Accepted Answer · answered Oct 03 '12 at 10:17

Like this?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim a As String
    Dim LastCol As Long

    '~~> Set this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    '~~> Get the last used Column
    LastCol = LastColumn(ws)

    '~~> Return Column Name from Column Number
    a = Split(ws.Cells(, LastCol).Address, "$")(1)

    MsgBox a
End Sub

Public Function LastColumn(Optional wks As Worksheet) As Long
    If wks Is Nothing Then Set wks = ActiveSheet
    LastColumn = wks.Cells.Find(What:="*", _
                After:=wks.Range("A1"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
End Function

Downboter: Comments please? – Siddharth Rout Oct 08 '14 at 08:29 — Siddharth Rout, Oct 08 '14 at 08:29

how to find the address of last used column using vba

1 Answers1