excel - variable - Error al buscar la última celda utilizada en VBA

seleccionar hasta la ultima celda con datos vba (11)

Agregaría a la respuesta de Siddarth Rout que la llamada CountA se puede omitir haciendo que Find devuelva un objeto Range, en lugar de un número de fila, y luego pruebe el objeto Range devuelto para ver si es Nothing (hoja de trabajo en blanco) .

Además, quisiera que mi versión de cualquier procedimiento de LastRow devuelva un cero para una hoja de trabajo en blanco, entonces puedo saber que está en blanco.

Cuando quiero encontrar el último valor de celda utilizada, utilizo:

Dim LastRow As Long LastRow = Range("E4:E48").End(xlDown).Row Debug.Print LastRow

Obtengo el resultado incorrecto cuando coloco un elemento único en una celda. Pero cuando pongo más de un valor en la celda, la salida es correcta. ¿Cuál es la razón detrás de esto?

Creé esta función de una sola parada para determinar la última fila, columna y celda, ya sea para datos, celdas formateadas (agrupadas / comentadas / ocultas) o formato condicional .

Sub LastCellMsg() Dim strResult As String Dim lngDataRow As Long Dim lngDataCol As Long Dim strDataCell As String Dim strDataFormatRow As String Dim lngDataFormatCol As Long Dim strDataFormatCell As String Dim oFormatCond As FormatCondition Dim lngTempRow As Long Dim lngTempCol As Long Dim lngCFRow As Long Dim lngCFCol As Long Dim strCFCell As String Dim lngOverallRow As Long Dim lngOverallCol As Long Dim strOverallCell As String With ActiveSheet If .ListObjects.Count > 0 Then MsgBox "Cannot return reliable results, as there is at least one table in the worksheet." Exit Sub End If strResult = "Workbook name: " & .Parent.Name & vbCrLf strResult = strResult & "Sheet name: " & .Name & vbCrLf ''DATA: ''last data row If Application.WorksheetFunction.CountA(.Cells) <> 0 Then lngDataRow = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Else lngDataRow = 1 End If ''strResult = strResult & "Last data row: " & lngDataRow & vbCrLf ''last data column If Application.WorksheetFunction.CountA(.Cells) <> 0 Then lngDataCol = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Else lngDataCol = 1 End If ''strResult = strResult & "Last data column: " & lngDataCol & vbCrLf ''last data cell strDataCell = Replace(Cells(lngDataRow, lngDataCol).Address, "$", vbNullString) strResult = strResult & "Last data cell: " & strDataCell & vbCrLf ''FORMATS: ''last data/formatted/grouped/commented/hidden row strDataFormatRow = StrReverse(Split(StrReverse(.UsedRange.Address), "$")(0)) ''strResult = strResult & "Last data/formatted row: " & strDataFormatRow & vbCrLf ''last data/formatted/grouped/commented/hidden column lngDataFormatCol = Range(StrReverse(Split(StrReverse(.UsedRange.Address), "$")(1)) & "1").Column ''strResult = strResult & "Last data/formatted column: " & lngDataFormatCol & vbCrLf ''last data/formatted/grouped/commented/hidden cell strDataFormatCell = Replace(Cells(strDataFormatRow, lngDataFormatCol).Address, "$", vbNullString) strResult = strResult & "Last data/formatted cell: " & strDataFormatCell & vbCrLf ''CONDITIONAL FORMATS: For Each oFormatCond In .Cells.FormatConditions ''last conditionally-formatted row lngTempRow = CLng(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(0))) If lngTempRow > lngCFRow Then lngCFRow = lngTempRow ''last conditionally-formatted column lngTempCol = Range(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(1)) & "1").Column If lngTempCol > lngCFCol Then lngCFCol = lngTempCol Next ''no results are returned for Conditional Format if there is no such If lngCFRow <> 0 Then ''strResult = strResult & "Last cond-formatted row: " & lngCFRow & vbCrLf ''strResult = strResult & "Last cond-formatted column: " & lngCFCol & vbCrLf ''last conditionally-formatted cell strCFCell = Replace(Cells(lngCFRow, lngCFCol).Address, "$", vbNullString) strResult = strResult & "Last cond-formatted cell: " & strCFCell & vbCrLf End If ''OVERALL: lngOverallRow = Application.WorksheetFunction.Max(lngDataRow, strDataFormatRow, lngCFRow) ''strResult = strResult & "Last overall row: " & lngOverallRow & vbCrLf lngOverallCol = Application.WorksheetFunction.Max(lngDataCol, lngDataFormatCol, lngCFCol) ''strResult = strResult & "Last overall column: " & lngOverallCol & vbCrLf strOverallCell = Replace(.Cells(lngOverallRow, lngOverallCol).Address, "$", vbNullString) strResult = strResult & "Last overall cell: " & strOverallCell & vbCrLf MsgBox strResult Debug.Print strResult End With End Sub

Los resultados se ven así:

Para obtener resultados más detallados, algunas líneas en el código pueden ser descomentadas:

Existe una limitación: si hay tablas en la hoja, los resultados pueden no ser confiables, así que decidí evitar ejecutar el código en este caso:

If .ListObjects.Count > 0 Then MsgBox "Cannot return reliable results, as there is at least one table in the worksheet." Exit Sub End If

Dado que la pregunta original es acerca de los problemas para encontrar la última celda, en esta respuesta enumeraré las diversas formas en que puede obtener resultados inesperados ; ver mi respuesta a "¿Cómo puedo encontrar la última fila que contiene datos en la hoja de Excel con una macro?" por mi decisión de resolver esto.

Empezaré expandiendo la respuesta de sancho.s y el comentario de GlennFromIowa , que agrega aún más detalles:

[...] uno tiene que decidir primero qué se considera usado. Veo al menos 6 significados. Cell tiene:
1) datos, es decir, una fórmula, que posiblemente da como resultado un valor en blanco;
2) un valor, es decir, una fórmula o constante no en blanco;
3) formateo;
4) formato condicional;
5) una forma (incluido el comentario) que se superpone a la celda;
6) participación en una tabla (objeto de lista).
¿Qué combinación quieres probar? Algunos (como Tablas) pueden ser más difíciles de probar, y algunos pueden ser raros (como una forma fuera del rango de datos), pero otros pueden variar según la situación (por ejemplo, fórmulas con valores en blanco).

Otras cosas que quizás quiera considerar:

A) ¿Puede haber filas ocultas (por ejemplo, autofiltro), celdas en blanco o filas en blanco?
B) ¿Qué tipo de rendimiento es aceptable?
C) ¿Puede la macro de VBA afectar el libro de trabajo o la configuración de la aplicación de alguna manera?

Con esto en mente, veamos cómo las formas comunes de obtener la "última célula" pueden producir resultados inesperados:

El .End(xlDown) de la pregunta se romperá más fácilmente (por ejemplo, con una sola celda no vacía o cuando haya celdas en blanco intermedias ) por los motivos explicados en la respuesta de Siddharth Rout aquí (la búsqueda de "xlDown es igual poco confiable. " ) 👎
Cualquier solución basada en Count ing ( CountA o Cells*.Count ) o .CurrentRegion también se romperá en presencia de celdas o filas en blanco 👎
Una solución que involucre .End(xlUp) para buscar hacia atrás desde el final de una columna, igual que CTRL + UP, buscará datos (las fórmulas que producen un valor en blanco se consideran "datos") en filas visibles (así que usarlo con autofiltro activado) puede producir resultados incorrectos ⚠️).
Debe tener cuidado de evitar las trampas estándar (para más detalles volveré a referirme a la respuesta de Siddharth Rout aquí, busque la sección "Buscar la última fila en una columna" ), como la codificación de la última fila ( Range("A65536").End(xlUp) ) en lugar de confiar en sht.Rows.Count .
.SpecialCells(xlLastCell) es equivalente a CTRL + END, devolviendo la celda inferior y derecha del "rango usado", por lo que todas las advertencias que se aplican a depender del "rango utilizado" se aplican también a este método. Además, el "rango utilizado" solo se restablece al guardar el libro de trabajo y al acceder a la worksheet.UsedRange xlLastCell , por lo que xlLastCell podría producir resultados obsoletos⚠️ con modificaciones no guardadas (por ejemplo, después de eliminar algunas filas). Vea la respuesta cercana por dotNET .
sht.UsedRange (descrito detalladamente en la respuesta de sancho.s aquí) considera los datos y el formato (aunque no el formato condicional) y restablece el "rango utilizado" de la hoja de trabajo , que puede ser o no lo que usted desea.
Tenga en cuenta que un error común ️ es usar .UsedRange.Rows.Count ⚠️, que devuelve el número de filas en el rango utilizado, no el último número de fila (serán diferentes si las primeras filas están en blanco), para detalles, consulte La respuesta de Newguy a ¿Cómo puedo encontrar la última fila que contiene datos en la hoja de Excel con una macro?
.Find permite encontrar la última fila con cualquier dato (incluidas las fórmulas) o un valor no en blanco en cualquier columna . Puede elegir si le interesan las fórmulas o los valores, pero la pega es que restablece los valores predeterminados en el diálogo Buscar de Excel ️️⚠️, lo que puede ser muy confuso para sus usuarios. También debe usarse con cuidado, vea la respuesta de Siddharth Rout aquí (sección "Encontrar la última fila en una hoja" )
Las soluciones más explícitas que verifican las Cells individuales en un bucle son generalmente más lentas que la reutilización de una función de Excel (aunque todavía puede ser un rendimiento), pero le permiten especificar exactamente lo que desea encontrar. Vea mi solución basada en UsedRange de UsedRange y VBA para encontrar la última celda con datos en la columna indicada: maneja filas ocultas, filtros, espacios en blanco, no modifica los valores predeterminados de Buscar y es bastante eficiente.

Cualquier solución que elija, tenga cuidado

utilizar Long lugar de Integer para almacenar los números de las filas (para evitar obtener Overflow con más de 65k filas) y
para especificar siempre la hoja de cálculo con la que está trabajando (es decir, Dim ws As Worksheet ... ws.Range(...) lugar de Range(...) )
al usar .Value (que es una Variant ), evite .Value <> "" implícitas como .Value <> "" ya que fallarán si la celda contiene un valor de error.

Estaba buscando una forma de imitar el CTRL + Shift + End , por lo que la solución dotNET es genial, excepto que con mi Excel 2010 necesito agregar un set si quiero evitar un error:

Function GetLastCell(sh As Worksheet) As Range Set GetLastCell = sh.Cells(1, 1).SpecialCells(xlLastCell) End Function

y cómo comprobar esto por ti mismo:

Sub test() Dim ws As Worksheet, r As Range Set ws = ActiveWorkbook.Sheets("Sheet1") Set r = GetLastCell(ws) MsgBox r.Column & "-" & r.Row End Sub

Me pregunto si nadie ha mencionado esto, pero la forma más fácil de obtener la última celda utilizada es:

Function GetLastCell(sh as Worksheet) As Range GetLastCell = sh.Cells(1,1).SpecialCells(xlLastCell) End Function

Esto esencialmente devuelve la misma celda que obtienes mediante Ctrl + Fin después de seleccionar Celda A1 .

Una advertencia: Excel realiza un seguimiento de la celda más abajo a la derecha que alguna vez se usó en una hoja de trabajo. Entonces, si, por ejemplo, ingresas algo en B3 y algo más en H8 y luego eliminas el contenido de H8 , al presionar Ctrl + Fin te llevará a la celda H8 . La función anterior tendrá el mismo comportamiento.

Sin embargo, esta pregunta busca encontrar la última fila usando VBA, creo que sería bueno incluir una fórmula de matriz para la función de la hoja de cálculo, ya que se visita con frecuencia:

{=ADDRESS(MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0),COLUMN(D:D))}

Debe ingresar la fórmula sin corchetes y presionar Mayús + Ctrl + Intro para convertirla en una fórmula de matriz.

Esto le dará la dirección de la última celda utilizada en la columna D.

Una nota importante a tener en cuenta al usar la solución ...

LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

... es asegurar que su variable LastRow sea de tipo Long :

Dim LastRow as Long

De lo contrario, terminará recibiendo errores de OVERFLOW en ciertas situaciones en los libros de .XLSX

Esta es mi función encapsulada que incluyo en varios usos del código.

Private Function FindLastRow(ws As Worksheet) As Long '' -------------------------------------------------------------------------------- '' Find the last used Row on a Worksheet '' -------------------------------------------------------------------------------- If WorksheetFunction.CountA(ws.Cells) > 0 Then '' Search for any entry, by searching backwards by Rows. FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row End If End Function

En cuanto a la forma correcta de encontrar la última celda utilizada, primero se debe decidir qué se considera usado y luego seleccionar un método adecuado . Concedo al menos dos significados:

Usado = no en blanco, es decir, que tiene datos .
Used = "... en uso, es decir, la sección que contiene datos o formato ". Este es el criterio utilizado por Excel en el momento de guardar . Ver también this Este criterio generalmente produce resultados inesperados, pero también puede explotarse intencionalmente (con menos frecuencia, con seguridad), por ejemplo, para resaltar o imprimir regiones específicas, que eventualmente pueden no tener datos. Y, por supuesto, es deseable como criterio para el rango que se utilizará al guardar un libro de trabajo.

Cómo encontrar la última celda utilizada depende de lo que desee (su criterio) .

Para el criterio 1, sugiero leer esta respuesta . Tenga en cuenta que UsedRange se cita como no confiable. Creo que es engañoso (es decir, "injusto" para UsedRange ), ya que UsedRange simplemente no está destinado a informar la última celda que contiene datos. Por lo tanto, no debería usarse en este caso, como se indica en esa respuesta. Ver también este comentario .

Para el criterio 2, UsedRange es la opción más confiable , en comparación con otras opciones también diseñadas para este uso. Incluso hace que sea innecesario guardar un libro de trabajo para asegurarse de que la última celda esté actualizada. Ctrl + End irá a una celda incorrecta antes de guardar ("La última celda no se restablece hasta que guarde la hoja de cálculo", en http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx . Es una referencia antigua, pero a este respecto es válida).

Hay otra trampa más:
El Criterio 2 no tiene en cuenta el formato condicional. Uno puede tener celdas formateadas, basadas en fórmulas, que no son detectadas por UsedRange o Ctrl + End . En la figura, la última celda es B3, ya que el formateo se aplicó explícitamente a ella. Las celdas B6: D7 tienen un formato derivado de una regla de formato condicional, y esto ni siquiera lo UsedRange .

En cuanto a su pregunta específica : ¿Cuál es la razón detrás de esto?

Su código usa la primera celda de su rango E4: E48 como trampolín, para saltar con End(xlDown) .

El resultado "erróneo" se obtendrá si no hay celdas no en blanco en su rango que no sea el primero. Luego, saltas en la oscuridad , es decir, en la hoja de trabajo (¡debes notar la diferencia entre el espacio en blanco y la cadena vacía !).

Tenga en cuenta que:

Si su rango contiene celdas no en blanco contiguas, entonces también dará un resultado incorrecto.
Si solo hay una celda que no está en blanco, pero no es la primera, su código aún le dará el resultado correcto.

NOTA : Tengo la intención de hacer de esto una "publicación de una parada" donde puede usar la forma Correct para encontrar la última fila. Esto también cubrirá las mejores prácticas a seguir cuando encuentre la última fila. Y, por lo tanto, seguiré actualizando cada vez que me encuentre con un nuevo escenario / información.

Formas poco fiables de encontrar la última fila

Algunas de las formas más comunes de encontrar la última fila son muy poco confiables y, por lo tanto, nunca deben usarse.

UsedRange
xlDown
CountA

UsedRange NUNCA se debe usar para encontrar la última celda que tiene datos. Es muy poco confiable. Prueba este experimento.

Escriba algo en la celda A5 . Ahora cuando calcule la última fila con cualquiera de los métodos que figuran a continuación, le dará 5. Ahora coloree la celda A10 roja. Si ahora usa cualquiera de los siguientes códigos, igual obtendrá 5. Si usa Usedrange.Rows.Count ¿qué obtiene? No será 5.

Aquí hay un escenario para mostrar cómo funciona UsedRange .

xlDown es igualmente poco confiable.

Considera este código

lastrow = Range("A1").End(xlDown).Row

¿Qué pasaría si hubiera solo una celda ( A1 ) que tuviera datos? ¡Llegará a la última fila en la hoja de trabajo! Es como seleccionar la celda A1 y luego presionar la tecla Finalizar y luego presionar la tecla de flecha hacia abajo . Esto también le dará resultados no confiables si hay celdas en blanco en un rango.

CountA tampoco es confiable porque le dará un resultado incorrecto si hay celdas en blanco intermedias.

Y por lo tanto, uno debe evitar el uso de UsedRange , xlDown y CountA para encontrar la última celda.

Encontrar la última fila en una columna

Para encontrar la última fila en Col E usa esto

With Sheets("Sheet1") LastRow = .Range("E" & .Rows.Count).End(xlUp).Row End With

Si notas que tenemos a . antes de Rows.Count . A menudo elegimos ignorar eso. Vea THIS pregunta sobre el posible error que pueda obtener. Siempre aconsejo usar . antes de Rows.Count y Columns.Count . Esa pregunta es un escenario clásico en el que el código fallará porque Rows.Count devuelve 65536 para Excel 2003 y 1048576 anteriores, y 1048576 para Excel 2007 y 1048576 posteriores. De Columns.Count similar, Columns.Count devuelve 256 y 16384 , respectivamente.

El hecho anterior de que Excel 2007+ tiene 1048576 filas también enfatiza el hecho de que siempre debemos declarar la variable que mantendrá el valor de la fila como Long lugar de Integer contrario obtendrá un error de Overflow .

Encontrar la última fila en una hoja

Para encontrar la última fila Effective en la hoja, use esto. Observe el uso de Application.WorksheetFunction.CountA(.Cells) . Esto es necesario porque si no hay celdas con datos en la hoja de trabajo, entonces .Find le dará Run Time Error 91: Object Variable or With block variable not set

With Sheets("Sheet1") If Application.WorksheetFunction.CountA(.Cells) <> 0 Then lastrow = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Else lastrow = 1 End If End With

Buscar la última fila en una tabla (ListObject)

Se aplican los mismos principios, por ejemplo, para obtener la última fila en la tercera columna de una tabla:

Sub FindLastRowInExcelTableColAandB() Dim lastRow As Long Dim ws As Worksheet, tbl as ListObject Set ws = Sheets("Sheet1") ''Modify as needed ''Assuming the name of the table is "Table1", modify as needed Set tbl = ws.ListObjects("Table1") With tbl.ListColumns(3).Range lastrow = .Find(What:="*", _ After:=.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row End With End Sub

Sub lastRow() Dim i As Long i = Cells(Rows.Count, 1).End(xlUp).Row MsgBox i End Sub sub LastRow() ''Paste & for better understanding of the working use F8 Key to run the code . dim WS as worksheet dim i as long set ws = thisworkbook("SheetName") ws.activate ws.range("a1").select ws.range("a1048576").select activecell.end(xlup).select i= activecell.row msgbox "My Last Row Is " & i End sub

sub last_filled_cell() msgbox range("a65536").end(xlup).row end sub

"aquí a65536 es la última celda en la columna donde se probó este código en excel sti72003" 200

y si está usando su "a1,048,576"

mi código es solo para que los principiantes entiendan los conceptos de qué fin (xlup) y otros comandos relacionados pueden hacer