Encontrar la última celda en una hoja de Excel

Una de las tareas frecuentes al escribir código Vba, muchas veces al modificar código de una macro grabada, es tener que determinar la última fila (o celda o columna) usada en la hoja.
Si nos basamos en una macro que hemos grabado al usar el método Ir-Especial-Última celda,

veremos esta sentencia

Range("A1").SpecialCells(xlCellTypeLastCell).Select

El problema con este método es que Excel recalcula la ubicación de la última celda usada sólo cuando guardamos el archivo. Esto significa que si ingresamos un valor en una celda remota y luego lo borramos, Excel seguirá considerando esa celda como la última. Y como si esto no fuera poco, también si cambiamos el formato de una celda remota, aún si ingresar ningún valor, Excel la considerará la última celda usada.

Para asegurarnos de encontrar siempre la última celda usada, es decir, la celda más remota que contiene algún valor, tenemos que usar un código basado en el método Range.Find

Sub ultima_celda()
    Dim UltimaFila As Long

    If WorksheetFunction.CountA(Cells) > 0 Then

        UltimaFila = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row

         MsgBox UltimaFila, vbInformation, "Ultima fila"

    End If

End Sub

Este código encuentra la última celda y nos informa cuál es la fila con un mensaje.

Este video nos muestra la diferencia entre ambos métodos

Manejo de fechas y tiempo en Excel.

En los albores de este blog, allí por el año 2006, publiqué una nota sobre cómo Excel maneja los datos de tiempo (horas y fechas). Por cuestiones que no vienen al caso, esa nota ya no está disponible. Si bien el tema resultará muy elemental para muchos de mis lectores, recibo no pocas consultas relacionadas con dificultades para realizar cálculos de horas o fechas en Excel.  Por lo que he resuelto reeditar la nota.

La forma en que Excel maneja las fechas y horas no es intuitiva, en particular por el hecho que cuando ingresamos una fecha en  una celda, o una hora, lo que Excel nos muestra es distinto de lo que Excel "ve".

width

Cuando ingresamos en una celda un valor que Excel interpreta como fecha (por ejemplo: 30/05/2013), en la celda y en la barra de fórmulas veremos "30/05/2013", pero si cambiamos el formato de la celda a "General" veremos el número 41424

Esto se debe a que en Excel las fechas son números de una serie. En esta serie el 1 representa el 1ro. de enero de 1900, el 2 el 02/01/1900 y así sucesivamente. Desde el 01/01/1900 al 30/05/2013 han transcurrido 41424 días.

De la misma manera podemos ingresar un número de la serie en la celda y  aplicar el formato de fecha para verla como tal

La primer fecha en la serie corresponde al 0 (el 00/01/1900). El motivo de la existencia de la fecha inexistente 00/01/1900 es representar horas que no están asociadas a una fecha en particular.

Esto último nos lleva a ver cómo Excel maneja las horas. Los datos horarios son la parte decimal del número que representa la fecha. Por ejemplo, las 12:00 del 30/05/2013 es el número 41424.5 (o 41424,5 para los países que usan la coma como separador de decimales).  Como en el ejemplo anterior podemos ingresar el número como tal y al cambiar el formato a fecha-hora, veremos

Los números de las horas resultan de dividir 1 por la cantidad de horas de un día (1/24).

Los números de la serie de minutos resultan de dividir 1 por la cantidad de minutos que hay en un día (24 X 60 = 1440).

Los segundos: 1/ 86400  (24 X 60 X 60 = 86400)

Cuestiones a tener en cuenta:

1 -  Excel convierte en fechas/horas todo dato que puede ser interpretado como tal. Por ejemplo, si tenemos un número de catálogo "25-4-2015", Excel lo convertirá en esa fecha y no en un código alfanumérico, que era nuestra intención

2 – Si ingresamos una fecha con una hora asociada mayor de 24 horas, Excel incrementa automáticamente la fecha al día siguiente. Por ejemplo, si ingresamos "30/05/2013 25:00" Excel lo convertirá automáticamente en "31/05/2013 01:00:00"

3 – Para obtener sumas de tiempos mayores a 24 horas sin que Excel los convierta en fecha + horas tenemos que usar el formato personalizado "[hh]:00"¨.

4 – La forma en que Excel reconoce como fechas depende de las definiciones regionales del sistema. Por ejemplo, en los Estados Unidos el valor "04/12/2012" será interpretado como el 12 de abril del 2012; en la Argentina como el 4 de diciembre del 2012.

5 – Excel usa ciertas reglas para determinar el siglo cuando ingresamos fechas usando solamente dos dígitos para el año. Por ejemplo, "05-12-29" será convertido en "05-12-2029"; "05-12-30" en "05-12-1930".

Evitar constantes en BUSCARV.

Si hay una línea en Excel que marca el paso de principiante a usuario intermedio, es el uso de BUSCARV. Esta función, como tantas otras funciones de búsqueda en Excel, extraer valores de una tabla de datos basándonos en un criterio de búsqueda.

Sin lugar a dudas, es una de las funciones más usadas en Excel. Sin embargo y desde el punto de vista de las buenas prácticas, tiene un problema al que pocos usuarios prestan atención: el tercer argumento de la función, el indicador_columna (la columna en la tabla de la cual se quiere extraer el dato). Veamos este ejemplo sencillo (que me vino a colación de una nota sobre normas de diseño en Excel que estoy escribiendo)

La fórmula en la celda F2 usa BUSCARV para extraer el número de teléfono de acuerdo al nombre que ponemos en E2.

Ahora, supongamos que insertamos en la tabla de datos una columna (Zona) entre los campos "Nombre" y "Teléfono"

Si bien los rangos de la función se han adaptado al cambio, el tercer argumento de la función es una constante. Y por lo tanto, ahora el resultado es la zona y no el teléfono.

En ciertos casos el error no es fácilmente identificable y puede ser crítico.

Las buenas prácticas en Excel nos indican que debemos evitar usar constantes en las fórmulas. En este caso podemos superar el problema usando la función COINCIDIR para crear una referencia dinámica.

Esto lo podemos hacer usando COINCIDIR junto con BUSCARV o, mejor en mi opinión, con INDICE.

Con BUSCARV usamos

=BUSCARV(F2;$A$2:$C$5;COINCIDIR(G$1;$A$1:$C$1;0);0)

con INDICE la fórmula es más corta

=INDICE($C$2:$C$5;COINCIDIR(F2;$A$2:$A$5;0))

Como Excel adapta los rangos dinámicamente al insertar, eliminar o mover filas y columnas, estas fórmulas mantienen siempre la referencia exacta y evitan errores inadvertidos.

Nótese que con la combinación de BUSCARV y COINCIDIR en nuestro ejemplo, podemos cambiar el data extraído con solo cambiar el texto en la celda G1.

Extraer valores únicos de rangos discontinuos

Esta nota trata sobre cómo extraer valores únicos de rangos que contienen más de una columna o rangos discontinuos.

Excel tiene dos métodos incorporados para esta tarea. En Excel 2007-2013 ambos se encuentran en la pestaña Datos: Filtro Avanzado y Quitar duplicados

Filtro Avanzado nos permite hacerlo con relativa facilidad usando la posibilidad, como mostramos en este video:



Con Quitar duplicados la técnica es un poco más elaborada, ya que incluye copiar la lista a un rango apartado y allí extraer los duplicados (si estamos interesados en guardar la lista original)




Las limitaciones de estos métodos comienzan cuando queremos extraer valores únicos de rangos discontinuos o de rangos que contienen más de una columna.

Podemos hacerlo con un código relativamente sencillo, similar al que mostramos en la nota sobre listas desplegables dependientes publicada hace poco. Este código se basa en el objeto Collection. El código es el siguiente


Sub extraerUnicos_Hoja()
'extraer valores unicos de rangos de varias columnas o no continuos
'Jorge Dunkelman - JLD Excel Blog, mayo 2013

    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range, rngDatos As Range, rngLista As Range
    Dim lCounter As Long
 
    Set rngDatos = Application.InputBox(prompt:="Seleccione rango/s con datos", Type:=8)
    Set rngLista = Application.InputBox(prompt:="Seleccione la primera celda de la lista", Type:=8)
 
    On Error Resume Next
    For Each rngCell In rngDatos
        collUnicos.Add rngCell, Cstr(rngCell)
    Next rngCell
    On Error GoTo 0
 
    lCounter = 0
    For Each vcollItem In collUnicos
        rngLista.Offset(lCounter, 0) = CStr(vcollItem)
        lCounter = lCounter + 1
    Next vcollItem
 
End Sub


Con este código definimos el rango que contiene los datos (que puede contener varias columnas o ser discontinuo, pero todos los datos deben estar en la misma hoja), definimos la celda desde donde queremos empezar a pegar la lista de registros únicos y el código la genera.

Por ejemplo, en esta matriz de 6 filas por tres columnas (18 valores) donde hay 5 valores únicos (a, b, c, d y e)



Para que este código sea realmente útil debemos agregar algunas líneas para manejar errores que pueden ocurrir durante el proceso (por ejemplo, si el usuario selecciona un rango de datos con una sola celda o si aprieta el botón Cancel del InputBox). El código completo es el siguiente

Sub extraerUnicos_Hoja()
'extraer valores unicos de rangos de varias columnas o no continuos
'Jorge Dunkelman - JLD Excel Blog, mayo 2013

    Dim collUnicos As New Collection
    Dim vcollItem As Variant
    Dim rngCell As Range, rngDatos As Range, rngLista As Range
    Dim lCounter As Long
 
    On Error GoTo errCancel 'si se aprieta Cancel
 
    Set rngDatos = Application.InputBox(prompt:="Seleccione rango/s con datos", Type:=8)
    If rngDatos.Count < 2 Then
        MsgBox "Debe seleccionar un rango con mas de dos celda", vbCritical
        Exit Sub
    End If
 
    Set rngLista = Application.InputBox(prompt:="Seleccione la primera celda de la lista", Type:=8)
    If rngLista.Count <> 1 Then
        MsgBox "Seleccione solamente una celda", vbCritical
        Exit Sub
    End If
 
    On Error Resume Next
    For Each rngCell In rngDatos
        collUnicos.Add rngCell, Cstr(rngCell)
    Next rngCell
    On Error GoTo 0
 
    lCounter = 0
    For Each vcollItem In collUnicos
        rngLista.Offset(lCounter, 0) = CStr(vcollItem)
        lCounter = lCounter + 1
    Next vcollItem
 
    Exit Sub
 
errCancel:
Exit Sub
End Sub

Lista desplegable con combobox dinámico

En el pasado mostramos que una de las limitaciones de crear listas desplegables con Validación de Datos es la falta de la propiedad "autocompletar". En una nota del año 2008 mostramos como usar un cuadro combinado (combobox) de la colección de controles ActiveX para superar esta limitación.

Desde entonces he recibido muchas consultas sobre el tema. La más común es cómo hacer que el control aparezca en la celda activa y desaparezca después de haber elegido el valor.

Es decir, queremos mimetizar el comportamiento de las listas creadas con validación de datos, pero usando el cuadro combinado para disfrutar de la propiedad "autocompletar".

La técnica para hacerlo incluye, obviamente programar eventos (Vba). Mostraré aquí un ejemplo sencillo, que puede aplicarse a todo modelo. En nuestro ejemplo definimos un rango que contiene los nombres de los días de la semana, que será la fuente de los valores del combobox, y un rango en la hoja donde queremos utilizar el control.

Los pasos son los siguientes:

1 – Creamos un nombre definido que se refiere al rango que contiene los días de la semana

En este ejemplo usamos el cuadro de nombres para crear el nombre con facilidad. El rango está en la hoja "valores".

2 – En la hoja "lista" incrustamos un control cuadro combinado (combobox) de la colección de controles ActiveX

Ubicamos el control en el lugar deseado (en nuestro caso sobre la celda B2) asegurándonos que ocupe toda la celda.
En la propiedad ListFillRange ponemos el nombre definido que se refiere a la lista de valores (dia_semana).

De ser necesario cambiamos la definición de la fuente a un tamaño adecuado cambiando la definición de la propiedad Font.

Al finalizar el proceso desactivamos el botón "Modo Diseño" (lo controles no funcionan cuando Excel se encuentra en modo de diseño).

3 – Definimos el rango de la hoja "Lista"", donde queremos que aparezcan los controles cuando seleccionamos alguna celda del rango. En nuestro ejemplo el rango es B2:B20 ("rngDia")

4 – Programamos el evento Worksheet_SelectionChange en la hoja "lista" de manera que cuando el usuario seleccione alguna celda del rango "rngDia", el cuadro combinado aparezca sobre esa celda. El código, que va en el módulo de la hoja, es el siguiente

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

Lo que hace este código lo siguientes:
- Vuelve visible el control (cuando la celda activa no está en el rango deseado, el control es invisible, lo que hacemos definiendo la propiedad Visible como False)
- Definimos la propiedad Top del control de manera que coincida con el ángulo superior izquierdo de la celda activa
- Definimos que celda debe recibir el valor elegido (la celda activa).
La última línea del código oculta el control si la celda activa no pertenece al rango donde queremos que aparezca la lista desplegable.

El modelo funciona así

Podemos mejorar este código de manera que al alto y ancho del control se adapten dinámicamente el alto y ancho de la celda, definiendo las propiedades Height y Wide en el código del evento

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Union(Target, Range("rngDia")).Address = Range("rngDia").Address Then
        With ComboBox1
            .Visible = True
            .Top = ActiveCell.Top
            .Height = ActiveCell.Height
            .Width = ActiveCell.Width
            .LinkedCell = ActiveCell.Address
        End With
    Else
        ComboBox1.Visible = False
    End If
End Sub

De la misma manera podemos definer dinámicamente otras propiedades como el contenido de la lista (ListFillRange), el número de valores a mostrar en el cuadro (ListRows), etc.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.