code - visual basic for applications excel

Obtención del rango de uso real

Question

Tengo una hoja de cálculo de Excel que tiene un botón.

Cuando llamo a la función usedRange (), el rango que devuelve incluye la parte del botón.

¿Hay alguna forma de obtener un rango real utilizado que contenga datos?

Answer 1

Aquí hay un par de funciones para devolver la última fila y columna de una hoja de trabajo, según la solución de Reafidy anterior.

    Function LastRow(ws As Object) As Long

        Dim rLastCell As Object
        On Error GoTo ErrHan
        Set rLastCell = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByRows, _
                                      xlPrevious)
        LastRow = rLastCell.Row

    ErrExit:
        Exit Function

    ErrHan:
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbExclamation, "LastRow()"
        Resume ErrExit

    End Function

    Function LastCol(ws As Object) As Long

        Dim rLastCell As Object
        On Error GoTo ErrHan
        Set rLastCell = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByColumns, _
                                      xlPrevious)
        LastCol = rLastCell.Column

    ErrExit:
        Exit Function

    ErrHan:
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbExclamation, "LastRow()"
        Resume ErrExit

    End Function

Answer 2

Qué tipo de botón, ni un control de formularios ni un control ActiveX deben afectar el rango utilizado.

Es un problema conocido que Excel no realiza un seguimiento del rango utilizado muy bien. Cualquier referencia al rango utilizado a través de VBA restablecerá el valor al rango actual utilizado. Así que intenta ejecutar este procedimiento sub:

Sub ResetUsedRng()
    Application.ActiveSheet.UsedRange 
End Sub 

Si no es así, es posible que tenga algún formato alrededor. Intente borrar / eliminar todas las celdas después de su última fila.

Respecto a lo anterior también ver:

Sugerencia para desarrolladores de Excel

Otro método para encontrar la última celda utilizada:

    Dim rLastCell As Range

    Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

Cambia la dirección de búsqueda para encontrar la primera celda usada.

Answer 3

Readify hizo una respuesta muy completa. Sin embargo, quería agregar la declaración End , puedes usar:

Encuentre la última celda usada, antes de un espacio en blanco en una columna:

Sub LastCellBeforeBlankInColumn()
Range("A1").End(xldown).Select
End Sub

Encuentra la última celda usada en una columna:

Sub LastCellInColumn()
Range("A" & Rows.Count).End(xlup).Select
End Sub

Encuentra la última celda, antes de un espacio en blanco en una fila:

Sub LastCellBeforeBlankInRow()
Range("A1").End(xlToRight).Select
End Sub

Encuentra la última celda usada en una fila:

Sub LastCellInRow()
Range("IV1").End(xlToLeft).Select
End Sub

Consulte here para obtener más información (y la explicación de por qué xlCellTypeLastCell no es muy confiable).

Answer 4

Utilizo el siguiente código vba para determinar el rango completo de filas utilizadas para la hoja de trabajo para luego acortar el rango seleccionado de una columna:

    Set rUsedRowRange = Selection.Worksheet.UsedRange.Columns( _
    Selection.Column - Selection.Worksheet.UsedRange.Column + 1)

También funciona al revés:

    Set rUsedColumnRange = Selection.Worksheet.UsedRange.Rows( _
    Selection.Row - Selection.Worksheet.UsedRange.Row + 1)

Answer 5

Public Sub FindTrueUsedRange(RowLast As Long, ColLast As Long)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    RowLast = 0
    ColLast = 0
    ActiveSheet.UsedRange.Select
    Cells(1, 1).Activate
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    On Error GoTo -1: On Error GoTo Quit
    Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Activate
    On Error GoTo -1: On Error GoTo 0
    RowLast = Selection.Row
    Cells(1, 1).Activate
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Activate
    ColLast = Selection.Column
Quit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    On Error GoTo -1: On Error GoTo 0
End Sub