Get last cell address in worksheet

[VBABEGINER]

how to get last cell address in worksheet..

 

[Fluff]

Re: how to get last cell address in worksheet

If you mean the last cell in UsedRange try

MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Address

 

[Johnny C]

Re: how to get last cell address in worksheet

LastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
LastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

There's an excellent article by Contextures with some different ways
http://www.contextures.com/rickrothsteinexcelvbasheet.html#lastrow

 

[VBABEGINER]

Re: how to get last cell address in worksheet

Thanks Johny for your reply.

Hi Fluff, it works. But it gives last column correctly, but last row number is incorrect...it takes reference of earlier column. Pls suggest..

If you mean the last cell in UsedRange try

MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Address

 

[Fluff]

Re: how to get last cell address in worksheet

The last cell is the intersection of the last used row & the last used column.
Is this not what you're getting?

 

[VBABEGINER]

Re: how to get last cell address in worksheet

No.

My actual last cell in worksheet is AU8.

Below is showing me, AU103. AU is correct. But in my Z column there is fix list which ends on 103 row. and hence it is showing that. But I wanted AU8 or whatever be the last in right side of worksheet.

LastColumn = Range("A1").SpecialCells(xlCellTypeLastCell).Address

The last cell is the intersection of the last used row & the last used column.
Is this not what you're getting?

 

[VBABEGINER]

Re: how to get last cell address in worksheet

The last cell is the intersection of the last used row & the last used column.
Is this not what you're getting?

This is my actual code..

Sub test()
Dim i As Integer
Dim j As Integer
lstrow = Range("Z" & Rows.Count).End(xlUp).Row
Dim str As String
Dim fndtx As Integer


Range("A2", Range("A2").End(xlDown)).Select
Selection.Copy
Range("AA2").Select
ActiveSheet.Paste


Range("AA2", Range("AA2").End(xlDown)).Select
Selection.TextToColumns Destination:=Range("AA2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
TrailingMinusNumbers:=True

LastColumn = Range("A1").SpecialCells(xlCellTypeLastCell).Address
'LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column


For j = 2 To lstrow


str = Range("Z" & j).Select
str = Range("Z" & j).Value


Selection.Copy


Columns("AA:AU").Select
Range("AA2").Activate
fndtx = Selection.Find(What:=str, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate


If str = fndtx Then


End If


Next

End Sub

 

[Fluff]

Re: how to get last cell address in worksheet

To show the last used row in the last used column, use this

    Dim UsdCols
    UsdCols = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    MsgBox Cells(Rows.Count, UsdCols).End(xlUp).Address

 

[Joe4]

Re: how to get last cell address in worksheet

My actual last cell in worksheet is AU8.

Below is showing me, AU103. AU is correct. But in my Z column there is fix list which ends on 103 row. and hence it is showing that. But I wanted AU8 or whatever be the last in right side of worksheet.

You are contradicting yourself. In column Z has entries up to row 103, then 103 is your last row, not row 8.

As was mentioned, the last cell in Excel is the intersection of the last column (AU) and last row (103). So AU103.

If you are looking for the last populated row in your last column, that is something different, and and can obtained like this:

    Dim lastColumn As Long
    Dim lastAddress As String
    
    lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
    lastAddress = Cells(Rows.Count, lastColumn).End(xlUp).Address(0, 0)
    MsgBox lastAddress

 

[VBABEGINER]

Re: how to get last cell address in worksheet

Thanks Fluff, for your help. Your solution gives me last column number. But as Joe explain me I understand and Joe's solution works for me here.. I got now AU8.

Hi Joe, Many thanks for kind co-operation. It gives me correctly.

Can you guys please help me in understand. I have list of values in Col Z. Now one by one I'm picking this cell value and find in this range from AA till AU8.

But I dont understand how to present this..

If the Z col value finds successfully in this range, then same row of Z value marked a color. But this color should go in A cell value...

Could you please suggest...

You are contradicting yourself. In column Z has entries up to row 103, then 103 is your last row, not row 8.

As was mentioned, the last cell in Excel is the intersection of the last column (AU) and last row (103). So AU103.

If you are looking for the last populated row in your last column, that is something different, and and can obtained like this:

    Dim lastColumn As Long
    Dim lastAddress As String
    
    lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
    lastAddress = Cells(Rows.Count, lastColumn).End(xlUp).Address(0, 0)
    MsgBox lastAddress