Get last cell address in worksheet

[VBABEGINER]

how to get last cell address in worksheet..

 

[VBABEGINER]

Re: how to get last cell address in worksheet

This is my code, actually. I am not getting fndtx address.

Sub test()
Dim i As Integer
Dim j As Integer
Dim str As String
Dim fndtx As Integer
Dim lastColumn As Long
Dim lastAddress As String


lstrow = Range("Z" & Rows.Count).End(xlUp).Row


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 = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
lastAddress = Cells(Rows.Count, lastColumn).End(xlUp).Address(0, 0)


'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:" & lastAddress).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
Z = fndtx.Address
Range("A" & Z).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With


End If


Next

End Sub

 

[Fluff]

Re: how to get last cell address in worksheet

Try this, note I have changed the dim statement for fndtx

Sub Mee()
    Dim fndtx As Range
    For J = 2 To lstrow
        str = Range("Z" & J).Value
        Set fndtx = Columns("AA:AU").Find(What:=str, After:=Range("AA2"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
        If Not fndtx Is Nothing Then Range("A" & J).Interior.Color = vbYellow
    Next J

End Sub

 

[VBABEGINER]

Re: how to get last cell address in worksheet

Hi Fluff, actually im using this line..

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

&lastAddress. Bcoz it will change every time. But here I observe that, It should require like this...Columns("AA:" & lastAddress) AA:AU, Not AA:AU8.

what can i do in this case...

Try this, note I have changed the dim statement for fndtx

Sub Mee()
    Dim fndtx As Range
    For J = 2 To lstrow
        str = Range("Z" & J).Value
        Set fndtx = Columns("AA:AU").Find(What:=str, After:=Range("AA2"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
        If Not fndtx Is Nothing Then Range("A" & J).Interior.Color = vbYellow
    Next J

End Sub

 

[Fluff]

Re: how to get last cell address in worksheet

You've totally lost me now.
If you only want to look in Col AA, why are you restricting the search to the last row of a completely different column?
Your original code was searching cols AA to AU, the code Joe4 supplied tells you the last used row in Col AU, not AA

 

[VBABEGINER]

Re: how to get last cell address in worksheet

My apologies Fluff..

The only reason finding the address is bcoz i want to find Z values in this ranges...My Col AA is fix, but then right side of columns are not fix.

Set fndtx

value will not get find we I not make this dynamic.

I hope u got it. Please suggest.. we are now at the end of stage...please.

You've totally lost me now.
If you only want to look in Col AA, why are you restricting the search to the last row of a completely different column?
Your original code was searching cols AA to AU, the code Joe4 supplied tells you the last used row in Col AU, not AA

 

[Fluff]

Re: how to get last cell address in worksheet

Which columns do you want to search?

 

[VBABEGINER]

Re: how to get last cell address in worksheet

Here, im trying to get Column name...i.e. AU. Next time it may AZ, next time it may AF...dynamic.

Sub test()
Dim i As Integer
Dim j As Integer
Dim str As String
Dim fndtx As Range
Dim lastColumn As Long
Dim lastAddress As String


lstrow = Range("Z" & Rows.Count).End(xlUp).Row


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 = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
lastAddress = Cells(Rows.Count, lastColumn).End(xlUp).Address


Dim lColumn As String
sColumn = Left(lastAddress, 3)
Dim rColumn As String
rColumn = Right(sColumn, 2)




For j = 2 To lstrow


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


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


If Not fndtx Is Nothing Then
Else
Range("A" & j).Interior.Color = vbGreen
End If


Next j


Columns("AA:" & lastAddress).Select
Selection.Delete Shift:=xlToLeft
End Sub

Which columns do you want to search?

 

[Fluff]

Re: how to get last cell address in worksheet

So you only want to search the last used column, is that right?

Also please do not keep posting your code, as it clutters up the thread.
& when posting code please use code tags (the # icon) rather than quote tags

 

[VBABEGINER]

Re: how to get last cell address in worksheet

Hey Fluff, one point i just want to share that, even though Im using manual "AA:AU" then also it not finds...Please..

Hi Fluff, actually im using this line..


&lastAddress. Bcoz it will change every time. But here I observe that, It should require like this...Columns("AA:" & lastAddress) AA:AU, Not AA:AU8.

what can i do in this case...

 

[VBABEGINER]

Re: how to get last cell address in worksheet

Ok, noted...

So you only want to search the last used column, is that right?

Also please do not keep posting your code, as it clutters up the thread.
& when posting code please use code tags (the # icon) rather than quote tags