Buscar la parte inferior de la hoja de trabajo de Excel en VBA

Me gustaría seleccionar al final de la hoja de trabajo, pero no debajo de lo que se usa / almacena. Podría tener 10.000 filas, pero ciertamente no tengo 65.536. No sabré con anticipación cuántas filas.

En Excel en sí (en las versiones recientes, de todos modos, Excel 97 no fue tan amable) puede presionar Ctrl + Fin para ir a la última fila y columna. Me gustaría la misma funcionalidad.

La forma más simple es comenzar desde abajo y search la última fila que contenga algo:
Rango ("a65536"). End (xlup) .row

Eso es elemental:

Selection.End(xlDown).Select 

(Encontré esto presionando ctrl + end mientras grabo una 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 

Sería más seguro asumir huecos y usar algo de XL: Cómo determinar celdas superiores e inferiores utilizadas en una matriz dispersa

Lo he usado previamente

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

Esta function devuelve no de la fila máxima y la columna máxima en la spreadsheet con algún contenido. tal vez sea útil para sbdy. por supuesto, esto es muy lento, pero por lo general no tenemos que verificar todas las filas y columnas, por lo que hay que ajustar los 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