viernes, 1 de septiembre de 2017

Referencias a partes de una Tabla de Excel mediante VBA


Las Tablas de Excel son un enorme apoyo para la programación en VBA, entre otras cosas,porque son unos objetos que llevan implícitos unos nombres de rango que podemos utilizar a la hora de diseñar nuestro código.

Una determinada Tabla de Excel ya tiene un nombre de rango que hace referencia a ella misma, pero también nombres de rango particulares para la fila de encabezados y para la fila de totales. También, cada una de las filas y de las columnas tienen unas formas de ser referenciadas.

Vamos a ver a continuación cuál es el código necesario para referirnos a esas partes de una tabla o a la tabla entera:

1) Para seleccionar la TABLA ENTERA (encabezados y totales incluidos)


    ActiveSheet.ListObjects("NombreDeMiTabla").Range.Select

2) Para seleccionar solo el CUERPO de la tabla (sin encabezados ni totales)


   ActiveSheet.ListObjects("
NombreDeMiTabla").DataBodyRange.Select

... o bien...

[NombreDeMiTabla].Select

(Al fin y al cabo, un nombre de tabla es un nombre de rango para VBA)

3) Para seleccionar solo la FILA DE ENCABEZADOS de la tabla 


     ActiveSheet.ListObjects("NombreDeMiTabla").HeaderRowRange.Select


4) Para seleccionar solo la FILA DE TOTALES de la tabla 


     ActiveSheet.ListObjects("NombreDeMiTabla").TotalsRowRange.Select

5) Para seleccionar la 3ª COLUMNA (entera o solo el cuerpo) de la tabla
 



     ActiveSheet.ListObjects("NombreDeMiTabla").ListColumns(3).Range.Select
     ActiveSheet.ListObjects("
NombreDeMiTabla").ListColumns(3).DataBodyRange.Select
6) Para seleccionar la 3ª FILA de datos de la tabla


     ActiveSheet.ListObjects("NombreDeMiTabla").ListRows(4).Range.Select


Mediante índices también podemos seleccionar cualquier elemento concreto dentro de los rangos que acabamos de conocer. Por ejemplo:

a) Para seleccionar el 3er encabezado


     ActiveSheet.ListObjects("NombreDeMiTabla").HeaderRowRange(3).Select

b) Para un dato concreto dentro del cuerpo de la tabla


    ActiveSheet.ListObjects("NombreDeMiTabla").DataBodyRange(3, 2).Select



José Manuel Pomares Medrano








49 comentarios:

  1. para poner el nombre de la variable es posible

    ResponderEliminar
  2. No entiendo esa afirmación...o ¿debería ser una pregunta?

    ResponderEliminar
  3. Hola, ¿Cómo sería para referenciar el título de una columna de la tabla? Por ejemplo el título 4. Esto con el fin de poder mover las columnas dentro de la tabla sin que afecte la referncia.

    ResponderEliminar
    Respuestas
    1. Hola, gracias por escribir.
      Puedes ver la respuesta en los apartados 5) y a) del artículo.
      Saludos

      Eliminar
  4. Para el caso 6 b), si yo quisiera seleccionar el dato que está en la columna (o campo) que se llama "Titulo 2" y la fila 3, independientemente de la ubicación en la tabla de dicho campo (porque quizas a posteriori lo mueva), ¿Podrías ayudarme a como sería la sintaxis correcta?

    ResponderEliminar
    Respuestas
    1. Sería:

      ActiveSheet.ListObjects("NombreDeMiTabla").ListColumns("Titulo 2").DataBodyRange.Cells(3,1)

      Saludos

      Eliminar
  5. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  6. en ua macro como borrar el contenido de una tabla ya que la misma varia su rango dependiendo de los datos.

    ResponderEliminar
  7. Buenas noches
    Haciendo referencia a la tabla y después el método ClearContents:

    [NombreDeMiTabla].ClearContents

    Independientemente del número de filas, se elimina el contenido
    Saludos

    ResponderEliminar
    Respuestas
    1. Buenas tardes. El comando sirve, sin embargo, existe uno que permita demás de borrar el contenido, elimine todas las filas de la tabla, de tal suerte que se muestre la tabla original, sin filas, obviamente, solo mostrará la primera, (que no son los encabezados) que es la que no se puede borrar. Gracias.

      Eliminar
  8. Buenos días,

    Muchas gracias por la información, es muy útil.

    Tengo una pregunta. ¿Cómo puedo hacer para seleccionar todas las celdas vacías de una columna específica de una tabla? El objetivo sería eliminar la fila completa de cada una de esas celdas.

    ResponderEliminar
    Respuestas
    1. Puedes grabar una macro que haga las siguientes acciones:
      1) Selecciona el rango afectado
      2) Ve a Inicio / Buscar y seleccionar / Ir a especial... / selecciona la opción "Celdas en blanco".
      3) Ve a Inicio / Eliminar / Filas de hoja (o filas de tabla si estás sobre una tabla de Excel)

      Una vez grabada la macro, copia el código y lo pegas donde corresponda.

      Espero haberte ayudado. Saludos

      Eliminar
  9. tengo una pregunta como hago para recorrer la primera columna y detectar filas vacías en la tabla para después mandar un mensaje que hacen falta datos y no pasar al siguiente paso de la macro

    gracias y slaudos.

    ResponderEliminar
  10. Hola, ¿Como puedo saber el numero de fila seleccionada (de una tabla creada)?
    No el numero de la hoja excel, el número de fila de la tabla (seleccionando una celda de ella).
    Gracias

    ResponderEliminar
    Respuestas
    1. Una posibilidad es utilizar

      ActiveCell.Row - Nº de filas por encima de la tabla

      Saludos

      Eliminar
  11. Creo que el siguiente caso no esta contemplado;
    Quiero seleccionar la fila X de una tabla,pero no toda la fila, me interesa desde la columna [ENE-01] hasta [ENE-15].
    Como se haría?

    ResponderEliminar
    Respuestas
    1. Este comentario ha sido eliminado por el autor.

      Eliminar
    2. Para los casos no contemplados puedes utilizar algo como:

      Range(Range("miTabla").cells(fil,col), Range("miTabla").Cells(fil,col))

      Donde miTabla es un ejemplo de nombre de tabla de Excel
      Donde fil y col son los números de fila y columna de la tabla (no de Excel)
      Saludos

      Eliminar
  12. Hola.
    Para activar o desactivar un filtro en una tabla primero debo saber si ya tiene un filtro activado o no para que no me de error.
    ¿Como puedo comprobarlo? ¿Como podría además saber si la celda activa actual está colocada dentro de una tabla o no?

    ResponderEliminar
    Respuestas
    1. Por ello, antes de activar o desactivar un filtro con VBA, debes poner:
      ActiveSheet.ListObjects("Tabla1").Range.AutoFilter Field:=1
      De esta forma "reinicias" el filtro para poder aplicar otro distinto

      Para saber si la celda activa está dentro de una tabla puedes utilizar un condicional, del tipo:

      If ActiveCell.Row > 10 And ActiveCell.Row < 20 And ActiveCell.Column > 5 And ActiveCell.Row < 15 Then ......

      Y si la tabla puede crecer o decrecer a lo ancho (por ejemplo), los números del código anterior deben ser dinámicos, por ejemplo aplicando una variable cuyo valor sea el resultado de.... (por ejemplo)

      Dim anchoTabla as Integer
      anchoTabla = Range(PrimeraCeldaTabla,PrimeraCeldaTabla.End(xlToRight)).Columns.Count

      De esta forma ya sabes el número actual de columnas de la tabla (solo deberías agregar a la variable las columnas que hay a la izquierda de la tabla).

      Saludos

      Eliminar
  13. Hola,
    si quisiera eliminar una fila de la tabla, el cual seria escogida de un listbox en donde se encuentra una lista de datos, ¿como se haria? perdone mi ignorancia, soy novato en esta materia de macros

    ResponderEliminar
  14. hola, como puedo hacer un macro que me permita ingresar datos al final de una tabla presionando un boton. tomando en cuenta que quiero apuntar a la tabla en especifico y no a la fila de la hoja, ya que la tabla podria moverla de ubicacion. existe algun comando que agregue una fila mas a la tabla? gracias

    ResponderEliminar
    Respuestas
    1. BUenos días:

      Debes escribir:

      ActiveSheet.ListObjects("NombreDeMiTabla").ListRows(1).Add

      ...para que te egregue una dila en primera posición de la tabla.

      Saludos

      Eliminar
  15. Cordial saludo, si ya seleccioné toda una columna de datos y la tengo guardada en una variable, ¿qué función de vba me sirve para determinar el tamaño de ese vector? Necesito recorrer todas las celdas de esa columna en un for y la tabla irá creciendo con el tiempo, entonces necesito una función que pueda determinar constantemente su dimensión para que constantemente recorra todas las celdas de la columna. Gracias!

    ResponderEliminar
    Respuestas
    1. Si tienes una columna guardada en una variable de objeto, puedes hacer:

      MsgBox miVariable.Rows.Count

      Y obtendrás el número de filas del objeto

      Espero haberte ayudado

      Eliminar
  16. Buenas tardes, intento quitar el formato de una hoja, la cual incluye una tabla, con el sigueinte codigo no funciona:

    Cells.Select
    Selection.ClearFormats

    Creo que podria hacerlo si pongo el nombre de la tabla, pero la cuestion esque la macro la voy a usar para ejecutarla en varias hojas, por ende varias tablas, diferentes nombres.

    Podrian ayudarme.?

    Desde ya muchas gracias.!!!

    ResponderEliminar
  17. Hola, al final encontre como hacerlo, pero me di cuenta que no resilvi el problema, porque lo que en verdad necesitaba era convertir la tabla a rango, y de esa poder eliminar columnas y filas para un reporte personalizado.

    Como voy a trabajar con diferentes tablas, lo que hice primero fue obtener el nombre de la tabla para luego poder eliminarla como tal y que solo quedara como rango normal.

    Sub EliminarTabla()

    'Declaro las variables
    Dim NombreTablaActiva As String
    Dim Tabla As ListObject

    Sheets("Hojaxxx").Select
    'Convierte la tabla en Rango, es decir quita el formato de Tabla
    NombreTablaActiva = ActiveCell.ListObject.Name

    'Quitar el formato de tabla
    ActiveSheet.ListObjects(NombreTablaActiva).TableStyle = ""

    'Elimina el formato de tabla y solo queda el contenido como un rano normal
    ActiveSheet.ListObjects(NombreTablaActiva).Unlist

    End sub

    Espero le sirva a alguien.

    Saludos.!

    ResponderEliminar
  18. Buenos días.
    Primero felicitarte por compartir tus grandes conocimientos.
    Este hilo es la mejor info en relación al manejo y referencias a tablas.
    Como podría seleccionar/copiar una tabla de otro libro sin necesidad de conocer la hoja que la contiene? Igual que cuando lo hago desde el libro activo, que no hay que nominar la hoja
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Considerando que:
      1) La tabla está en Libro1
      2) El código está en Libro2
      3) La tabla se llama Tabla1

      ... una posibilidad sencilla es:

      Workbooks("Libro1.xlsx").Activate
      [Tabla1].Copy
      Workbooks("Libro2.xlsx").Activate

      En ningún momento utilizas el nombre de la hoja. La clave es que si tienes activo el libro que contiene la tabla, a esta la puedes nombrar solo por su nombre.
      Saludos.

      Eliminar
  19. Muchas gracias por la respuesta, sólo que devuelvo el valor del registro n (pedido por parm) de la primera columna desde una función y, si puedo evitarlo, no me gustaría tener que volver a activar el libro sobre el que trabajo después de cada uso de la función.
    Saludos

    ResponderEliminar
    Respuestas
    1. Muchas gracias por la respuesta, sólo que devuelvo el valor del registro n (pedido por parm) de la primera columna desde una función y, si puedo evitarlo, no me gustaría tener que volver a activar el libro sobre el que trabajo después de cada uso de la función.
      Saludos

      Eliminar
  20. hola, como puedo escoger especificamente uno de los titulos de la tabla? el titulo esta ubicado visualmente en A1, pero para la tabla A1 es A2. Como me ubico en ese titulo? gracias

    ResponderEliminar
    Respuestas
    1. HOla Charles
      Lo tienes en el propio artículo:

      ActiveSheet.ListObjects("NombreDeMiTabla").HeaderRowRange(3).Select

      Donde debes cambiar el número 3 por el número de encabezado que quieras

      Saludos.

      Eliminar
  21. Hola muy buenas. Muchas gracias por los consejos.
    Tengo un problema, cómo puedo hacer una búsqueda en una determinada columna de la tabla. Utilizando una variable.
    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Puedes utilizar:
      MiVariable = WorksheetFuntion.VLookup(valor_buscado, Matriz, Índice columna, Ordenado)
      Por ejemplo.
      Saludos.

      Eliminar
  22. Hola, hay alguna manera de seleccionar varias columnas discontinuas de la tabla?

    ResponderEliminar
    Respuestas
    1. Sería algo así, por ejemplo:

      Sub seleccionDiscontinua()

      Dim vRng1 As Range, vRng2 As Range
      Set vRng1 = ActiveSheet.ListObjects("Tabla1").ListColumns(1).Range
      Set vRng2 = ActiveSheet.ListObjects("Tabla1").ListColumns(3).Range

      Application.Union(vRng1, vRng2).Select

      End Sub

      Saludos

      Eliminar
  23. hola y muchas gracias por su ayuda.
    como podría hacer para que una macro reconozca que se ha borrado hasta el último dato de la última fila de una tabla, luego como la fila quedó vacía entonces redimensione la tabla (sin la fila vacía).
    obviamente tendría que ocurrir en el evento worksheet_change de la hoja donde está la tabla.
    gracias por su atención.

    ResponderEliminar
  24. Hola!!! Me gustaría saber cómo puedo poner un nombre de tabla variable y usarlo. El nombre sería asi: “Tabla” & Sheet.index
    Gracias por su ayuda

    ResponderEliminar
    Respuestas
    1. Hola Isa.

      Sería algo como:

      Hoja1.ListObjects("MiTabla").Name = "Tabla" & ActiveSheet.Index

      Donde MiTabla es el nombre actual y "Tabla" ActiveSheet.Index será el nombre futuro.

      Saludos.

      Eliminar
    2. Muchísimas gracias por tu rápida respuesta. Creo que no me he expresado bien. Lo que quiero es asignar ese nombre a una variable para luego poder usarla. Te escribo el código:
      .ListObjects.Add(xlSrcRange, Range("A1:F1"), , xlYes).Name = "Tabla" & oSh.Index
      .ListObjects("Tabla" & oSh.Index).ShowAutoFilterDropDown = False

      No me deja crear una variable y asignarle "Tabla" & Osh.Index.
      Muchas gracias por todo

      Eliminar
    3. En teoría debe estar bien el código. Deberías saber qué es lo que te da el error, concretamente.
      ¿Seguro que OSh.Index devuelve algo que no es un error?
      Aunque oSh.Index devuelva un número de índice, al unirlo con "Tabla" genera un nombre que ya existe y por lo tanto un error?

      Mete en una variable de texto primero la cadena "Tabla"& oSh.Index para ver que todo eso no genera un error y para ver también que el nombre de tabla generado no está repetido y es válido.

      Saludos.

      Eliminar
    4. Lo de meter en una variable de texto la cadena ya lo he probado y me daba error. Ya lo he solucionado creando una función:
      Function nomtab() As String
      nomtab = "Tabla" & ActiveSheet.Index
      End Function

      Muchísimas gracias por todo. Espero que esto pueda ayudar a más personas. Saludos.

      Eliminar
  25. Tengo otro problema. Quiero rellenar la columna de una tabla (que se crea automáticamente al añadir una hoja nueva) con datos de la columna de otra tabla que está en otra hoja. Ya no sé qué probar. Gracias nuevamente por vuestra ayuda.

    ResponderEliminar
    Respuestas
    1. Podría ser algo como

      With Range("TablaDestino[ColumnaDestino]")
      .ClearContents
      Range("TablaOrigen[ColumnaOrigen]").Copy
      .Cells(1, 1).PasteSpecial Paste:=xlPasteValues
      End With


      Saludos

      Eliminar
    2. Gracias por vuestra ayuda, pero da Error en el método 'Range' de objeto '_Worksheet'

      Eliminar
    3. Pero... ¿has cambiado los nombres de tabla y de columna en los sitios correspondientes? (TablaDestino, ColumnaDestino, TablaOrigen, ColumnaOrigen) ... ¡¡¡Deben ser los nombres de tus tablas!!!

      Saludos

      Eliminar