Find for last value contain cell...

[somnath_it2006]

Hi All,

I want to find non-blank last cell of the sheet. I have a code but if there is any merge cell is there then its not working.

lngLastRow = ObjWBTarget.Sheets(ObjWSTarget.Name).Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row

lngLastCol = ObjWBTarget.Sheets(ObjWSTarget.Name).Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Above code gives the last cells column and row number.
But not working if merge cell is there.

Please help!!!!!

Thanks in advance....

 

[Colin Legg]

Hi,

If your definition of last used row includes the mergearea of the last cell you can account for it using the Range.MergeArea() property:

Sub test()
    Dim LastRow As Long
 
    LastRow = Get_Last_Used_Row(Sheet1.Cells)
 
    Debug.Print LastRow
 
End Sub
 
Function Get_Last_Used_Row(rngToCheck As Range) As Long
 
    Dim rngFound As Range
 
    Set rngFound = rngToCheck.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious)
 
    If rngFound Is Nothing Then
        Get_Last_Used_Row = rngToCheck.Row
 
    Else
        With rngFound.MergeArea
            Get_Last_Used_Row = .Row + .Rows.Count - 1
        End With
    End If
End Function

Same concept to be applied for the last column. Of course, in an ideal world you would want to try to get rid of those merged cells (IMO)....

Of course, if the mergearea of another cell (not the last cell) goes lower than the last cell, then this function would not account for it. If this is the issue then you could look at using the less reliable Range.SpecialCells(xlCellTypeLastCell) method.

Hope that helps...

 

[somnath_it2006]

Thanks for your reply Colin....
I used Range.SpecialCells(xlCellTypeLastCell) method, and its working fine.


Thanks.