Chirag R Raval
Member
Dear Sir,
I got a function for search used range of Last Cell of data to conver it to sub for
how to convert it to Sub Procedures??
hope your co-operation for understand things ..
for further work..
Regards,
Chirag
for
I got a function for search used range of Last Cell of data to conver it to sub for
how to convert it to Sub Procedures??
Code:
Public Function GetLastCell(InRange As range, SearchOrder As XlSearchOrder, _
Optional sheet As Worksheet, Optional ProhibitEmptyFormula As Boolean = False) As range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetLastCell
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
'
' This returns the last used cell in a worksheet or range. If InRange
' is a single cell, the last cell of the entire worksheet if found. If
' InRange contains two or more cells, the last cell in that range is
' returned.
' If SearchOrder is xlByRows (= 1), the last cell is the last
' (right-most) non-blank cell on the last row of data in the
' worksheet's UsedRange. If SearchOrder is xlByColumns
' (= 2), the last cell is the last (bottom-most) non-blank cell in the
' last (right-most) column of the worksheet's UsedRange. If SearchOrder
' is xlByColumns + xlByRows (= 3), the last cell is the intersection of
' the last row and the last column. Note that this cell may not contain
' any value.
' If SearchOrder is anything other than xlByRows, xlByColumns, or
' xlByRows+xlByColumns, an error 5 is raised.
'
' Sheet is an optional specifier of which sheet to search. If omitted,
' the ActiveSheet is used.
'
' ProhibitEmptyFormula indicates how to handle the case in which the
' last cell is a formula that evaluates to an empty string. If this setting
' is omitted for False, the last cell is allowed to be a formula that
' evaluates to an empty string. If this setting is True, the last cell
' must be either a static value or a formula that evaluates to a non-empty
' string. The default is False, allowing the last cell to be a formula
' that evaluates to an empty string.
'''''''''''''''''''''''''
' Example:
' a b c
' d e
' f g
'
' If SearchOrder is xlByRows, the last cell is 'g'. If SearchOrder is
' xlByColumns, the last cell is 'e'. If SearchOrder is xlByRows+xlByColumns,
' the last cell is the intersection of the row containing 'g' and the column
' containing 'e'. This cell has no value in this example.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
Dim R As range
Dim LastCell As range
Dim LastR As range
Dim LastC As range
Dim SearchRange As range
Dim LookIn As XlFindLookIn
Dim RR As range
If sheet Is Nothing Then
Set WS = ActiveSheet
Else
Set WS = sheet
End If
If ProhibitEmptyFormula = False Then
LookIn = xlFormulas
Else
LookIn = xlValues
End If
Select Case SearchOrder
Case XlSearchOrder.xlByColumns, XlSearchOrder.xlByRows, _
XlSearchOrder.xlByColumns + XlSearchOrder.xlByRows
' OK
Case Else
Err.Raise 5
Exit Function
End Select
With WS
If InRange.Cells.count = 1 Then
Set RR = .UsedRange
Else
Set RR = InRange
End If
'Set R = RR(RR.Cells.Count)
Set R = RR(1, 1)
If SearchOrder = xlByColumns Then
Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
searchdirection:=xlPrevious, MatchCase:=False)
ElseIf SearchOrder = xlByRows Then
Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, MatchCase:=False)
ElseIf SearchOrder = xlByColumns + xlByRows Then
Set LastC = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
searchdirection:=xlPrevious, MatchCase:=False)
Set LastR = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, MatchCase:=False)
Set LastCell = Application.Intersect(LastR.EntireRow, LastC.EntireColumn)
Else
Err.Raise 5
Exit Function
End If
End With
Set GetLastCell = LastCell
End Function
Public Function GetFirstCell(InRange As range, SearchOrder As XlSearchOrder, _
Optional sheet As Worksheet, _
Optional ProhibitEmptyFormula As Boolean = False) As range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetFirstCell
' This returns the first cell in a range or worksheet.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
Dim R As range
Dim C As range
Dim LookIn As XlFindLookIn
Dim RR As range
Select Case SearchOrder
Case XlSearchOrder.xlByColumns, XlSearchOrder.xlByRows, _
XlSearchOrder.xlByColumns + XlSearchOrder.xlByRows
' OK
Case Else
Err.Raise 5
Exit Function
End Select
If ProhibitEmptyFormula = False Then
LookIn = xlFormulas
Else
LookIn = xlValues
End If
If sheet Is Nothing Then
Set WS = ActiveSheet
Else
Set WS = sheet
End If
With WS
If InRange.Cells.count = 1 Then
Set RR = .UsedRange
Else
Set RR = InRange
End If
If RR(1, 1).Formula <> vbNullString Then
Set GetFirstCell = RR(1, 1)
Exit Function
End If
If SearchOrder = xlByColumns Then
Set R = RR.Find(what:="*", after:=RR.Cells(1, 1), _
LookIn:=LookIn, LookAt:=xlPart, SearchOrder:=xlByColumns, _
searchdirection:=xlNext, MatchCase:=False)
ElseIf SearchOrder = xlByRows Then
Set R = RR.Find(what:="*", after:=RR.Cells(1, 1), _
LookIn:=LookIn, LookAt:=xlPart, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
ElseIf SearchOrder = xlByRows + xlByColumns Then
Set R = RR.Cells(1, 1)
Else
Err.Raise 5
Exit Function
End If
End With
Set GetFirstCell = R
End Function
hope your co-operation for understand things ..
for further work..
Regards,
Chirag
for