Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata
Public Function GetUsedRange(ws As Worksheet) As Range
 Dim lRow As Integer, lCol As Integer, fCol As Integer, fRow As Integer
 fCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlLeft).Column
 lCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
 fRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlDown).Row
 lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 Set GetUsedRange = ws.Range(Cells(fRow, fCol), Cells(lRow, lCol))
End Function

*** 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

Mind = Blown, can't beleive it took me so long to find this! Put a string in and random cell on a new sheet and test!

Public Sub lRow()
 Dim lRow As Long
 lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 Debug.Print lRow
End Sub


Update: After running into a spreadsheet without data that was left cenetered I realized I wanted to code my "lrow/lcol" statements better and build a WB with random data and tried to find it, this is what I ended up with:

Public lColLeft
Public lColLeftLet
Public lColRight
Public lColRightLet
Public Sub FindLast()
 
Dim rng As Range, ws As Worksheet
Set ws = Application.ActiveSheet
'Set rng = ws.Range("A1:A2")

'Set Vars
Call LastColumns

'Using the CELLS(row,column) reference inside of your RANGE() reference:
'Set rng = ws.Range(Cells(row,col),Cells(row,col)
'Set rng = ws.Range(Cells(1, 1), Cells(2, 1))

'Find Range Last Col Starting from Left & Moving Right & Last Row of Said Column From Bottom
'lColRightlRow = lRowOfCol(ColNum:=lColRight)
'Set rng = ws.Range(Cells(1, lColRight), Cells(lColRightlRow, lColRight))

'Find Range Last Col Starting from Right & Moving Left & Last Row of Said Column From Bottom Up
'lColLeftlRow = lRowOfCol(ColNum:=lColLeft)
'Set rng = ws.Range(Cells(1, lColLeft), Cells(lColLeftlRow, lColLeft))

'Find Range Last Col Starting from Right & Moving Left & Last Row of Said Column From Top Down
'lColLeftlRow = lRowOfCol(ColNum:=lColLeft, Endxl:="DOWN")
'Set rng = ws.Range(Cells(1, lColLeft), Cells(lColLeftlRow, lColLeft))

rng.Select

End Sub
Public Sub LastColumns()
'End Moving Left - Returns Number
    lColLeft = Cells(1, Columns.Count).End(xlToLeft).Column
'End Moving Left - Returns Letter
    lColLeftLet = Split(Cells(1, lColLeft).Address, "$")(1)
'End Moving Right - Returns Number
    lColRight = Range("A1").End(xlToRight).Column
'End Moving Right - Returns Letter
    lColRightLet = Split(Cells(1, lColLeft).Address, "$")(1)
End Sub
Public Function lRowOfCol(Optional ByVal ColNum As Long, Optional ByVal ColLet As String, Optional ByVal Endxl As String) As Long
    If StrComp(Endxl, "DOWN", vbTextCompare) = 0 Then
     If ColNum > 0 Then
      lRowOfCol = Cells(1, ColNum).End(xlDown).Row
     Else
      lRowOfCol = Range(ColLet & "1").End(xlDown).Row
     End If
    Else
     If ColNum > 0 Then
      lRowOfCol = Cells(Rows.Count, ColNum).End(xlUp).Row
     Else
      lRowOfCol = Range(ColLet & "500").End(xlUp).Row
     End If
    End If
End Function
Sub Test()
Result = lRowOfCol(ColNum:=1)
 Debug.Print Result
Result = lRowOfCol(ColLet:="A")
 Debug.Print Result
Result = lRowOfCol(ColLet:="A", Endxl:="DOWN")
 Debug.Print Result
Result = lRowOfCol(ColLet:="A", Endxl:="UP")
 Debug.Print Result
Result = lRowOfCol(ColNum:=1)
 Debug.Print Result
Result = lRowOfCol(ColNum:=1, Endxl:="Up")
 Debug.Print Result
Result = lRowOfCol(ColNum:=1, ColLet:="A")
 Debug.Print Result
End Sub


'''''PUBLICVARS'''''
Public lCol
Public lColLet
Public lRow
Public fBlank
'''''ENDPUBVARS'''''
'''''FINDLAST'''''
Public Sub FindLast()

'Find Last Column in Number Form
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

'Find Last Column Letter
    lColLet = Split(Cells(1, lCol).Address, "$")(1)

'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

'Find the First Blank Cell in Column A
    fBlank = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
 
End Sub
'''''ENDFINDLAST'''''
'''''FINDLAST'''''
Public Sub FindLast(strSheet)

Dim ws As Worksheet
Set ws = strSheet

'Find Last Column in Number Form
    lCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

'Find Last Column Letter
    lColLet = Split(Cells(1, lCol).Address, "$")(1)

'Find the last non-blank cell in column A(1)
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
'Find the First Blank Cell in Column A
    fBlank = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

End Sub
'''''ENDFINDLAST'''''

.Range("A2:" & Split(Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column).Address, "$")(1) & Cells(Rows.Count, 1).End(xlUp).Row)
("A1:A" & Application.ActiveSheet.UsedRange.Rows.Count)

Entire Worksheet:


Dim x as Range
Set x = Worksheets("Sheet name").Cells 
Set x = Worksheets("Sheet name").UsedRange
  • No labels