Geek Answers Handbook

Find the bottom of an Excel worksheet in VBA

I would like to select the bottom of the worksheet, but not lower than what is used / stored. I can have 10,000 lines, but of course I do not have 65,536. I will not know in advance how many lines.

In Excel itself (in recent versions, Excel 97 wasn’t so kind anyway), you can press Ctrl + End to go to the last row and column. I need the same functionality.

+5
source share
6 answers

The easiest way is to start from the bottom and work to find the last line that contains something:
Range ("a65536"). End (xlup) .row

+4
source

This is elementary:

Selection.End(xlDown).Select

( , ctrl + end .)

+3
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
+2

- XL: /

+1

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

no max row max . , sbdy. , , , .

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
0

More articles:


All Articles