Welcome to FreeSoftwareServers Confluence Wiki

*** https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba

https://www.excelcampus.com/vba/find-last-row-column-cell/

https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter/12797190#12797190

https://superuser.com/questions/1414357/clear-cells-to-right-of-selected-cell-in-excel-vba-cant-guess-column-letter-h?noredirect=1#comment2134745_1414357


Function GetlColFromRng(Rng As Range) As Integer
    GetlColFromRng = Rng.Columns(Rng.Columns.Count).Column
End Function
Public Function lRowOfCol(Optional ByVal ColNum As Long, Optional ByVal ColLet As String, Optional ByVal EndXL As String, Optional WS As Worksheet) As Long
   If WS Is Nothing Then
    Set WS = ActiveSheet
   End If
    If StrComp(EndXL, "DOWN", vbTextCompare) = 0 Then
     If ColNum > 0 Then
      lRowOfCol = WS.Cells(1, ColNum).End(xlDown).Row
     Else
      lRowOfCol = WS.Range(ColLet & "1").End(xlDown).Row
     End If
    Else
     If ColNum > 0 Then
      lRowOfCol = WS.Cells(Rows.Count, ColNum).End(xlUp).Row
     Else
      On Error GoTo ErrorHandler
      lRowOfCol = WS.Range(ColLet & Rows.Count).End(xlUp).Row
     End If
    End If

Exit Function
ErrorHandler:
 lRowOfCol = 1
End Function
Public Sub FindLast(WS As Worksheet)
 lCol = Get_lCol(WS)
 lColLet = Split(Cells(1, lCol).Address, "$")(1)
 fCol = Get_fCol(WS)
 fRow = Get_fRow(WS)
 lrow = Get_lRow(WS)
 fBlank = Get_fBlank(WS)
End Sub
Public Function GetUsedRange(WS As Worksheet) As Range
 WS.Activate
 Dim lrow As Integer, lCol As Integer, fCol As Integer, fRow As Integer
 fCol = Get_fCol(WS)
 lCol = Get_lCol(WS)
 fRow = Get_fRow(WS)
 lrow = Get_lRow(WS)
 Set GetUsedRange = WS.Range(Cells(fRow, fCol), Cells(lrow, lCol))
End Function
Public Function Get_lCol(WS As Worksheet) As Integer
 Dim sEmpty As Boolean
 On Error Resume Next
 sEmpty = IsWorksheetEmpty(Worksheets(WS.Name))
 If sEmpty = False Then
  Get_lCol = WS.Cells.Find(What:="*", after:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  If IsMerged(Cells(1, Get_lCol)) = True Then
   Get_lCol = Cells(1, Get_lCol).MergeArea.Columns.Count
  End If
 Else
  Get_lCol = 1
 End If
End Function
Public Function Get_lRow(WS As Worksheet) As Integer
 Dim IsSheetEmpty As Boolean
 IsSheetEmpty = IsWorksheetEmpty(WS)
 If IsSheetEmpty = False Then
  On Error Resume Next
  Get_lRow = WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 Else
  Get_lRow = 1
 End If
End Function
Public Function Get_fRow(WS As Worksheet) As Integer
 Dim IsSheetEmpty As Boolean
 IsSheetEmpty = IsWorksheetEmpty(WS)
 If IsSheetEmpty = False Then
  Dim Rng As Range, rcell As Range
  Get_fRow = 0
  Set Rng = Range(Cells(1, 1), Cells(1, Columns.Count))
  For Each rcell In Rng.Cells
   If rcell.Value <> "" Then
    Get_fRow = 1
    Exit For
   End If
  Next rcell
  If Get_fRow = 0 Then
   Get_fRow = WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlRight).Row
  End If
 Else
  Get_fRow = 1
 End If
End Function
Public Function Get_fCol(WS As Worksheet) As Integer
 Dim IsSheetEmpty As Boolean
 IsSheetEmpty = IsWorksheetEmpty(WS)
 If IsSheetEmpty = False Then
  On Error GoTo ErrorHandle
  Get_fCol = WS.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlLeft).Column
 Else
ErrorHandle:
  Get_fCol = 1
 End If
End Function

Public Function Get_lrCol(WS As Worksheet) As Integer
 Dim sEmpty As Boolean
 sEmpty = IsWorksheetEmpty(Worksheets(WS.Name))
 If sEmpty = False Then
  fRow = Get_fRow(WS)
  lCol = Get_lCol(WS)
  Get_lrCol = WS.Cells(fRow, lCol).End(xlToLeft).Column
 Else
  Get_lrCol = Columns.Count
 End If
End Function
Public Function Get_fBlank(WS As Worksheet) As Integer
 Get_fBlank = WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).Row
 If Get_fBlank = 0 Then
  Get_fBlank = 1
 End If
End Function
Public Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function
Public Sub GetColLet(Var As Range)
    ColLetter = Split(Var.Address, "$")(1)
End Sub
Public Function GetColInt(ColLet As String) As Integer
 GetColInt = Columns(ColLet).Column
End Function
  • No labels