último valor vacias vacia una ultimo ultima seleccionar registro pegar para macro insertar hasta fila encontrar datos cual con celdas celda buscar excel vba excel-vba

excel - valor - seleccionar hasta la ultima celda con datos vba



¿Cómo puedo encontrar la última fila que contiene datos en la hoja de Excel con una macro? (12)

Aquí hay una solución para encontrar la última fila, la última columna o la última celda. Aborda el dilema de estilo de referencia A1 R1C1 para la columna que encuentra. Desearía poder dar crédito, pero no puedo encontrar / recordar de dónde lo saqué, así que "¡Gracias!" a quien sea que publicó el código original en algún lugar.

Sub Macro1 Sheets("Sheet1").Select MsgBox "The last row found is: " & Last(1, ActiveSheet.Cells) MsgBox "The last column (R1C1) found is: " & Last(2, ActiveSheet.Cells) MsgBox "The last cell found is: " & Last(3, ActiveSheet.Cells) MsgBox "The last column (A1) found is: " & Last(4, ActiveSheet.Cells) End Sub Function Last(choice As Integer, rng As Range) '' 1 = last row '' 2 = last column (R1C1) '' 3 = last cell '' 4 = last column (A1) Dim lrw As Long Dim lcol As Integer Select Case choice Case 1: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 Case 2: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 Case 3: On Error Resume Next lrw = rng.Find(What:="*", _ After:=rng.Cells(1), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row lcol = rng.Find(What:="*", _ After:=rng.Cells(1), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Last = Cells(lrw, lcol).Address(False, False) If Err.Number > 0 Then Last = rng.Cells(1).Address(False, False) Err.Clear End If On Error GoTo 0 Case 4: On Error Resume Next Last = rng.Find(What:="*", _ After:=rng.Cells(1), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 Last = R1C1converter("R1C" & Last, 1) For i = 1 To Len(Last) s = Mid(Last, i, 1) If Not s Like "#" Then s1 = s1 & s Next i Last = s1 End Select End Function Function R1C1converter(Address As String, Optional R1C1_output As Integer, Optional RefCell As Range) As String ''Converts input address to either A1 or R1C1 style reference relative to RefCell ''If R1C1_output is xlR1C1, then result is R1C1 style reference. ''If R1C1_output is xlA1 (or missing), then return A1 style reference. ''If RefCell is missing, then the address is relative to the active cell ''If there is an error in conversion, the function returns the input Address string Dim x As Variant If RefCell Is Nothing Then Set RefCell = ActiveCell If R1C1_output = xlR1C1 Then x = Application.ConvertFormula(Address, xlA1, xlR1C1, , RefCell) ''Convert A1 to R1C1 Else x = Application.ConvertFormula(Address, xlR1C1, xlA1, , RefCell) ''Convert R1C1 to A1 End If If IsError(x) Then R1C1converter = Address Else ''If input address is A1 reference and A1 is requested output, then Application.ConvertFormula ''surrounds the address in single quotes. If Right(x, 1) = "''" Then R1C1converter = Mid(x, 2, Len(x) - 2) Else x = Application.Substitute(x, "$", "") R1C1converter = x End If End If End Function

¿Cómo puedo encontrar la última fila que contiene datos en una columna específica y en una hoja específica?


Debería usar .End(xlup) pero en lugar de usar 65536 es posible que desee usar:

sheetvar.Rows.Count

De esa manera funciona para Excel 2007, que creo que tiene más de 65536 filas


La primera función mueve el cursor a la última fila no vacía en la columna. La segunda función imprime esa fila de columnas.

Selection.End(xlDown).Select MsgBox (ActiveCell.Row)


Me gustaría agregar una forma más confiable utilizando UsedRange para encontrar la última fila utilizada:

lastRow = Sheet1.UsedRange.Row + Sheet1.UsedRange.Rows.Count - 1

De manera similar para encontrar la última columna utilizada, puede ver esto

Resultado en la ventana Inmediato:

?Sheet1.UsedRange.Row+Sheet1.UsedRange.Rows.Count-1 21


Qué tal si:

Sub GetLastRow(strSheet, strColumn) Dim MyRange As Range Dim lngLastRow As Long Set MyRange = Worksheets(strSheet).Range(strColum & "1") lngLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row End Sub

Re comentar

Esta

Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

Devolverá el número de fila de la última celda incluso cuando solo una celda de la última fila tenga datos.


Simple y rápido:

Dim lastRow as long Range("A1").select lastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

Ejemplo de uso:

cells(lastRow,1)="Ultima Linha, Last Row. Youpi!!!!" ''or Range("A" & lastRow).Value = "FIM, THE END"


Todas las soluciones que dependen de comportamientos incorporados (como .Find y .End ) tienen limitaciones que no están bien documentadas (ver here para más detalles).

Necesitaba algo que:

  • Encuentra la última celda no vacía (es decir, que tiene cualquier fórmula o valor , incluso si es una cadena vacía) en una columna específica
  • Se basa en primitivos con un comportamiento bien definido
  • Funciona de manera confiable con filtros automáticos y modificaciones de usuario
  • Se ejecuta lo más rápido posible en 10.000 filas (para que se ejecute en un controlador Worksheet_Change sin sentirse lento)
  • ... con un rendimiento que no se cae de un acantilado con datos accidentales o formateos puestos al final de la hoja (en ~ 1M filas)

La solución a continuación:

  • Utiliza UsedRange para encontrar el límite superior para el número de fila (para hacer la búsqueda de la verdadera "última fila" rápidamente en el caso común donde está cerca del final del rango utilizado);
  • Retrocede para encontrar la fila con datos en la columna dada;
  • ... usando arreglos VBA para evitar acceder a cada fila individualmente (en caso de que haya muchas filas en el UsedRange debemos omitir)

(Sin pruebas, lo siento)

'' Returns the 1-based row number of the last row having a non-empty value in the given column (0 if the whole column is empty) Private Function getLastNonblankRowInColumn(ws As Worksheet, colNo As Integer) As Long '' Force Excel to recalculate the "last cell" (the one you land on after CTRL+END) / "used range" '' and get the index of the row containing the "last cell". This is reasonably fast (~1 ms/10000 rows of a used range) Dim lastRow As Long: lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row - 1 '' 0-based '' Since the "last cell" is not necessarily the one we''re looking for (it may be in a different column, have some '' formatting applied but no value, etc), we loop backward from the last row towards the top of the sheet). Dim wholeRng As Range: Set wholeRng = ws.Columns(colNo) '' Since accessing cells one by one is slower than reading a block of cells into a VBA array and looping through the array, '' we process in chunks of increasing size, starting with 1 cell and doubling the size on each iteration, until MAX_CHUNK_SIZE is reached. '' In pathological cases where Excel thinks all the ~1M rows are in the used range, this will take around 100ms. '' Yet in a normal case where one of the few last rows contains the cell we''re looking for, we don''t read too many cells. Const MAX_CHUNK_SIZE = 2 ^ 10 '' (using large chunks gives no performance advantage, but uses more memory) Dim chunkSize As Long: chunkSize = 1 Dim startOffset As Long: startOffset = lastRow + 1 '' 0-based Do '' Loop invariant: startOffset>=0 and all rows after startOffset are blank (i.e. wholeRng.Rows(i+1) for i>=startOffset) startOffset = IIf(startOffset - chunkSize >= 0, startOffset - chunkSize, 0) '' Fill `vals(1 To chunkSize, 1 To 1)` with column''s rows indexed `[startOffset+1 .. startOffset+chunkSize]` (1-based, inclusive) Dim chunkRng As Range: Set chunkRng = wholeRng.Resize(chunkSize).Offset(startOffset) Dim vals() As Variant If chunkSize > 1 Then vals = chunkRng.Value2 Else '' reading a 1-cell range requires special handling <http://www.cpearson.com/excel/ArraysAndRanges.aspx> ReDim vals(1 To 1, 1 To 1) vals(1, 1) = chunkRng.Value2 End If Dim i As Long For i = UBound(vals, 1) To LBound(vals, 1) Step -1 If Not IsEmpty(vals(i, 1)) Then getLastNonblankRowInColumn = startOffset + i Exit Function End If Next i If chunkSize < MAX_CHUNK_SIZE Then chunkSize = chunkSize * 2 Loop While startOffset > 0 getLastNonblankRowInColumn = 0 End Function


Function LastRow(rng As Range) As Long Dim iRowN As Long Dim iRowI As Long Dim iColN As Integer Dim iColI As Integer iRowN = 0 iColN = rng.Columns.count For iColI = 1 To iColN iRowI = rng.Columns(iColI).Offset(65536 - rng.Row, 0).End(xlUp).Row If iRowI > iRowN Then iRowN = iRowI Next LastRow = iRowN End Function


Public Function GetLastRow(ByVal SheetName As String) As Integer Dim sht As Worksheet Dim FirstUsedRow As Integer ''the first row of UsedRange Dim UsedRows As Integer '' number of rows used Set sht = Sheets(SheetName) ''''UsedRange.Rows.Count for the empty sheet is 1 UsedRows = sht.UsedRange.Rows.Count FirstUsedRow = sht.UsedRange.Row GetLastRow = FirstUsedRow + UsedRows - 1 Set sht = Nothing End Function

sheet.UsedRange.Rows.Count: recuperar el número de filas utilizadas, no incluir una fila vacía encima de la primera fila utilizada

si la fila 1 está vacía y la última fila utilizada es 10, UsedRange.Rows.Count devolverá 9, no 10.

Esta función calcula el número de la primera fila del número UsedRange plus de filas de UsedRange.


Public Function LastData(rCol As Range) As Range Set LastData = rCol.Find("*", rCol.Cells(1), , , , xlPrevious) End Function

Uso ?lastdata(activecell.EntireColumn).Address


function LastRowIndex(byval w as worksheet, byval col as variant) as long dim r as range set r = application.intersect(w.usedrange, w.columns(col)) if not r is nothing then set r = r.cells(r.cells.count) if isempty(r.value) then LastRowIndex = r.end(xlup).row else LastRowIndex = r.row end if end if end function

Uso:

? LastRowIndex(ActiveSheet, 5) ? LastRowIndex(ActiveSheet, "AI")


sub test() msgbox Worksheets("sheet_name").Range("A65536").End(xlUp).Row end sub

Esto es buscar el valor en la columna A debido a "A65536"