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.