Find bottom of Excel worksheet in VBA

I'd like to select to the bottom of the worksheet, but not below what is used/stored. I might have 10,000 rows, but I certainly don't have 65,536. I won't know ahead of time how many rows.

In Excel it开发者_运维技巧self (in recent versions, anyway; Excel 97 wasn't so kind) you can press Ctrl + End to be taken to the last row and column. I'd like the same functionality.

The simplest way is to start at the bottom and work up to find the last row that contains something:

Range("A65536").end(xlup).row

That's elementary:

Selection.End(xlDown).Select

(Found this out by pressing ctrl + end while recording a macro.)

Public Sub Blank_Row_Remover() ' Start of Macro Code

'Deletes the Entire Row within the Selection if _
Some of the Cells Within the Selection Contain No Data.

Dim Start_Cell, End_Cell, Data_Info, End_Column, This_Column As Variant

Application.ScreenUpdating = False
Application.StatusBar = "Please Stand By, ('Removing Blank Rows...' ~ Macro In Progress)..."

    Call Data_Info_Selection(Start_Cell, End_Cell, Data_Info, End_Column, This_Column) ' Direct Method

    For Each Cell In Selection
        Cell.Formula = Replace(Cell.Formula, Cell.Formula, Trim(Cell.Formula)) {Rids Extra Spaces}
        'If InStr(Cell.Value, "Labels:") Then Cell.EntireRow.Clear 'Searching for a Particular String to Remove a Row
        'If InStr(Cell.Value, "   ") Then Cell.EntireRow.Clear 'Searching for another Particular String to Remove a Row {Like 4 Spaces in a Cell that Keeps it from Reading as a Blank}
    Next
        On Error Resume Next
            Selection.SpecialCells(xlBlanks).EntireRow.Delete
        On Error GoTo 0

    'Call Data_Info_Selection(Start_Cell, End_Cell, Data_Info, End_Column, This_Column) ' Direct Method

Application.ScreenUpdating = True
End Sub
Public Function Data_Info_Selection(ByRef Start_Cell, End_Cell, Data_Info, End_Column, This_Column As Variant)

Application.ScreenUpdating = False
Application.StatusBar = "Please Stand By, ('Selecting Partial Columns' ~ Macro In Progress)..."

Start_Cell = ActiveCell.Address
Start_Cell_Text = Range(Start_Cell).Text

Orginal_Start_Cell = Range(Start_Cell).Address

If Start_Cell_Text = "" Then
    Dim Cells As Range
        For Each Cell In Selection.Cells
            If Cell = "" Then
                Start_Cell = Cell.Address
            Else
                Start_Cell = Cell.Address
                Exit For
            End If
        Next
End If

    This_Column = Mid(Start_Cell, 2, 1) 'ColumnNum = ActiveCell.Column
        If Range(Start_Cell).Text = "" Then
            End_Column = This_Column & ActiveCell.Row
            End_Cell = Range(End_Column).Address
        Else
            End_Column = This_Column + "65536"
            End_Cell = Range(End_Column).End(xlUp).Address
            Start_Cell = Range(Orginal_Start_Cell).Address
        End If

    Data_Info = Range(Start_Cell, End_Cell).Address
    Range(Data_Info).Select

End Function
Public Sub Select_Partial_Data_Start_Cell() ' (This Seems to Work and is Cleaner and Simplier)

Application.ScreenUpdating = False
Application.StatusBar = "Please Stand By, ('Selecting Partial Data' ~ Macro In Progress)..."

Dim myLastRow As Long
Dim myLastColumn As Long

    Start_Cell = ActiveCell.Address
     On Error Resume Next
        myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
        myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
        myLast_Cell = Cells(myLastRow, myLastColumn).Address
    myRange = Start_Cell & ":" & myLast_Cell
    'If InStr(1, myLast_Cell, "104876", 1) Then myLast_Cell = "$F$1105"

Range(myRange).Select
Application.ScreenUpdating = True

End Sub' End of Macro Code

It would be safer to assume gaps and use something from XL: How to Determine Top/Bottom Used Cells in a Sparse Array

I've used this previously

'This finds the last row in thr worksheet
LR = Cells(Rows.Count, 18).End(xlUp).Row
Range(Cells(LR, 1), Cells(LR, 35)).Select

This function returns no of max row and max column in the worksheet with some content. maybe it will be usefull for sbdy. of course this is very slow, but usually we don't have to check all the rows and columns so one have to adjust the loops.

Public Function content_area(shName As String) As Variant
Dim res(1 To 2) As Integer
Dim ark As Worksheet

Set ark = ThisWorkbook.Sheets(shName)
nCol = 0
nRow = 0

For i = 1 To ark.Columns.Count
    temp = ark.Cells(ark.Cells(1, i).EntireColumn.Rows.Count, i).End(xlUp).Row
    If temp > nCol Then nCol = temp
Next
For i = 1 To ark.Rows.Count
    temp = ark.Cells(i, ark.Cells(i, 1).EntireRow.Columns.Count).End(xlToLeft).Column
    If temp > nRow Then nRow = temp
Next

res(1) = nCol
res(2) = nRow

content_area = res
End Function

'There are many ways to do this, 'some incorrect,

'the End(xlDown) and SpecialCells(xlCellTypeLastCell) etc... have everything to go wrong 'they might skip some spaces because they are hidden or some other thing

'I prefer using .find to look for stuff like that just to make sure

' The Easiest method:

Dim LastCell   As Range
Set LastCell = Cells(Rows.Count, Columns.Count)

MsgBox LastCell.Address

'(basically counts the number of rows and columns and uses those numbers to get last positions)

' My main method:

Dim FinalCell as range
Set FinalCell = Cells.Find(What:="", _
                After:=Range("a1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                searchorder:=xlByColumns, _
                searchdirection:=xlPrevious, _
                MatchCase:=False)
msgbox FinalCell.Address

'(It starts at the cell a1, 'and goes backwords,

'because there's no space to the left or above, 'it goes to end of the sheet)