Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado

Trucos de Excel Avanzado

Muchas veces cuando estamos resolviendo un problema en el cual empleamos VBA o macros es necesario contar con algunos ejemplos que nos puedan ser de utilidad para resolver los casos que tengamos que resolver.

En la siguiente lista de trucos de Excel Avanzado se encuentra una relación de temas relevantes, para la compresión de los mismos se requiere tener algún conocimiento de VBA o Macros a fin de poder sacarle el máximo provecho a alguno de estos, esperamos su consultas o sugerencias sobre los mismos.

Encuentra en esta lista más de 150 trucos para usar en Excel, todos los ejemplos corresponden a macros útiles en Excel, y pueden funcionar en Excel 2013, Excel 2016 y Excel 2019. 

  1. Validar DNI en Excel
  2. Cambiar puntos por comas en Excel
  3. Date Picker
  4. Convertir temperatura en Excel
  5. Sorteo en Excel con Macros
  6. RemoveDuplicates VBA Excel
  7. Dim en VBA
  8. Habilitar Macros en Excel para Mac
  9. VBA Ancho de columna
  10. Evitar parpadeo en Excel (VBA)
  11. Guardar como PDF en VBA
  12. Exportar varias hojas de Excel a PDF VBA
  13. Doble condicional en VBA
  14. Crear hipervínculos con VBA
  15. Pasar datos de tabla a Listbox en Excel
  16. Mostrar u ocultar filas con Macros
  17. Macro para insertar imagen en correo
  18. ¿Cómo enviar correos masivos?
  19. Eliminar Filas Según Criterio
  20. Enviar correo con html
  21. Manipular Formatos en VBA
  22. Uso Goto
  23. Proceso Masivo de Datos
  24. HIPERVINCULOS DENTRO DEL COMBOBOX
  25. OPTIONBUTTONS EN UN FORMULARIO
  26. Fechas en Formulario con VBA
  27. Saltos de Página con Macros
  28. Mensaje de Bienvenida con VBA
  29. Llenar Listbox con VBA
  30. Simular usuario y Password en VBA
  31. Quitar Filtros con VBA
  32. ¿Qué es un Palíndromo?
  33. ¿Qué es un inputbox?
  34. Guardar y cerrar libro con macros
  35. Funciones de Fecha
  36. For Each en VBA
  37. ¿Cómo determinar el mayor de 4 números con VBA?
  38. Copiar Hojas con Macros
  39. Controlar el Zoom en VBA
  40. BeforeSave en VBA
  41. Keypress en VBA
  42. Seleccionar carpeta en VBA
  43. CheckBox en VBA
  44. El Modelo de Objetos de Excel
  45. VBA Guardar Como
  46. Quitar los filtros en VBA
  47. Debug.Print en VBA
  48. Quitar duplicados en VBA
  49. On Error en VBA
  50. Wait en VBA
  51. Macro para abrir y cerrar archivos en Excel
  52. Error 7 en VBA
  53. Control LABEL en VBA
  54. Guardar libro en VBA
  55. GetOpenFilename en VBA
  56. Función String en VBA
  57. Vectores en VBA
  58. Salto de línea en VBA
  59. Generar números primos en Excel
  60. Pantalla completa en Excel (VBA)
  61. Símbolos Patrios de Portugal
  62. Símbolos patrios de Ecuador
  63. Personajes principales de Los Simpson
  64. Símbolos Patrios de Chile
  65. Símbolos Patrios de EL SALVADOR
  66. Símbolos Patrios de Bolivia
  67. Personajes Principales de SAILOR MOON
  68. Identificar color de celda en Excel
  69. Catalogo de imágenes (automóviles)
  70. Lista Pokemon Excel
  71. Seleccionar rango en VBA
  72. ¿Qué es ScreenUpdating?
  73. ¿Qué significa application.displayalerts?
  74. RefEdit
  75. Busqueda de datos con listbox - Aplicado al top 100 de las marcas mas recordadas por los peruanos
  76. Proteger y Desproteger todas las hojas
  77. Función para Sumar Datos por Color
  78. Sumar solo celdas en Negritas
  79. Mostrar filtros en la barra de estado
  80. Alinear un gráfico a un determinado rango
  81. Ocultar algunas flechas de selección en un Filtro
  82. Marcar las Celdas Duplicadas
  83. Reemplazar celdas en blanco con algun valor
  84. Coincidir los colores de una serie con los de las celdas de datos
  85. 5 Trucos de VBA necesarios
  86. Crear y seleccionar rangos con nombres
  87. Alternar bandas de colores con Macros
  88. Seleccionar la primera fila o columna en blanco
  89. Determinar con VBA si un archivo se encuentra abierto
  90. Seleccionar y dar formato a todos los rangos con nombres
  91. Resaltar la fila y columna activas
  92. Desproteger una hoja al abrir un libro con Macros
  93. Guardar rango de celdas como PDF
  94. Ejemplo de Números Aleatorios Únicos
  95. Ordenar datos con macros - VBA EXCEL
  96. Manejo de colores con macros en excel
  97. Enviar archivo adjunto en Email con VBA
  98. Cambiar de categoría de una UDF
  99. ¿Cómo buscar un valor en un rango de celdas en VBA?
  100. Extraer primera letra de la palabra (Acrónimos)
  101. ¿Cómo ejecutar macros al abrir un archivo en Excel?
  102. Combinar dos columnas
  103. Ordenar Hojas en excel con macros
  104. Crear un Archivo por cada hoja de Excel
  105. Uso de colores para autofiltro en VBA
  106. Crear hojas de una lista (VBA)
  107. Ideas para concatenar celdas con VBA o Macros
  108. Contar palabras en Excel aplicando VBA Macros
  109. Verificar un formato de fecha con Macros
  110. La Desviación Estándar en VBA
  111. Concatenar el contenido de un rango de celdas (mail recordatorio)
  112. ¿Qué es el OptionButton en VBA y cómo se usa?
  113. Listar nombres de archivos en excel con macros
  114. Unir contenido de Celdas
  115. ¿Cómo se usan los botones de opción en VBA?
  116. Arreglos Multidimensionales en VBA
  117. Manejo de Hojas de Excel con macros
  118. ¿Cómo se usan las casillas de verificación en VBA?
  119. Catálogos de imágenes con Macros
  120. Excel Avanzado: Manejo de Libros de Excel con Macros
  121. Eliminar Validación de datos con macros
  122. Eliminar tíldes con Macros
  123. Cambiar un color de letra en función de la fecha
  124. Convertir números en letras con Macros en Excel
  125. ¿Cómo encontrar celdas combinadas?
  126. VBA - Objeto WORKBOOKS
  127. Intercalar filas en blanco con Macros o VBA
  128. Algunos trucos para optimizar macros en Excel
  129. Uso de WorksheetFunction.RandBetween
  130. Eliminación de comentarios en Hojas Excel
  131. Listar los links existentes con VBA
  132. Ejemplo Application.DisplayAlerts
  133. Eliminar los hipervínculos de la hoja activa
  134. Operaciones básicas con archivo y carpetas
  135. Uso de WorksheetFunction.Vlookup
  136. Formulario de Excel Avanzado para el ingreso de documentos de Salida de un Almacén
  137. Encontrar información eliminando filas
  138. Tabla dinámica para análisis de Quejas y Reclamos - VBA
  139. Principales propiedades de un checkbox en un userform
  140. Generador de Horarios en Excel
  141. Calculo de posiciones de partidos de la eliminatoria Brasil 2014
  142. Combobox Dependientes
  143. Validar el ingreso de un email en un textbox
  144. Validar que solo existan letras y números en un textbox
  145. Activar o Desactivar el recalculo automático (F9) en una función
  146. Identificar la última fila en uso con VBA
  147. Ejemplo de uso de Solver con Macros
  148. Cambiar los colores de fondo con VBA
  149. Uso de Resize en Offset
  150. Mostrar cuadro de dialogo "Abrir" donde se elige un archivo
  151. ¿Cómo mostrar todas las hojas ocultas?
  152. Uso de la función PROPER
  153. Creación de un listado de Archivos
  154. Buscar Objetivo (con macros)
  155. Buscar y reemplazar masivo (parte1)

30 comentarios

  1. Interesante Blog, es gusto lo q uno quiere encontrar ... mas adelante are mis contribuciones ..

  2. Un proyecto de VBA lo puedes proteger al colocar una clave. De tal forma que no puedan cambiar o copiar la programación.
    Para esto ingresa en el ambiente de VBA y realiza lo siguiente:
    • Selecciona el proyecto.
    • Da un clic derecho sobre el proyecto
    • Elige la opción Propiedades de VBAProject…

  3. Sino tengo cargado el complemento de Power Pivot ¿puedo visualizar y operar un archivo que tenga esta aplicación?de no ser asi, ¿se puede crear un "ejecutable" para que otros usuarios si lo puedan ver?

  4. Como se puede obtener de una lista de excel de una viñeta un elemento de otra viñeta sin el uso de macros

  5. Cuando se trabaja con base de datos en hojas de excel, se necesita tener en las columnas de tus tablas elementos que ya se han definido anteriormente. Esto se necesita hacer debido a que cuando se llena las hojas se puede colocar el mismo elemento pero con mayúscula, en plural, etc. Cuando se filtra se contabiliza estos elementos de manera separada y causa errores. Es por ello que se recomienda utilizar la herramienta VALIDACIÓN DE DATOS, en criterio de validación se utiliza la lista, se ubica los elementos a elegir, se arrastra y se tiene una lista. Con este sencillo ejemplo se tiene una lista de elementos que uno mismo ha señalado y no habrá confusiones a la hora de filtrar estos elementos.

  6. Hola mira tengo un excel con macros, que tiene fecha creacion del 2013 y yo lo modifique pero cambio solo la fecha modificación, que puedo hacer para cambiar la fecha de creacion?, es para un trabajo para que no detecten que lo edite en vez de crear. gracias

  7. Hola ... tengo el siguiente caso :

    En una hoja de la planilla Excel coloco una matriz (3x3) partiendo desde la celda A7 hasta la celda C9, ahora bien me doy un amplio rango para ordenar los números de esta matriz, entre la celda A1 hasta celda C15 (en las restantes celdas no hay datos) y requiero que se ordene de mayor a menor pero en la misma ubicación.

    Al hacer una macro utilizando todo el rango (desde A1 hasta C15) las coloca siempre ordenadas a partir de la celda A1 y obviamente si lo efectúo manualmente y reduzco el rango a las que las contiene datos me las deja como quiero.

    Alguna macro o instrucción me permite realizar este procedimiento.

    De ante mano gracias y saludos

  8. Hola que tal.

    quisiera hacer una consulta. Tengo un problema al ejecutar:

    Private Sub Worksheet_Activate()
    Sheets("GUIAS").Cells(4, 2) = Format(Now, "MM/DD/YYYY")
    End Sub

    Me sale error de compilación, no se puede encontrar en proyecto o biblioteca.

    La cree en excel 2003 pero trato de ejecutarla en 2010 y 2013 y sale ese error. Podrían ayudarme? Gracias 😉

    • Ojo con las comillas dobles, ya que las copié tal cual las tienes y me sacó error.

      Ensaya esto:

      Sub Worksheet_Activate()
      Cells(4, 2).FormulaR1C1 = Format(Now, "MM / DD / YYYY")
      End Sub

      Si te sirve, es con mucho gusto...

  9. como puedo leer un dato en negrita de una tabla dinamica?

  10. Buenos días,

    tengo varias filas combinadas ya que cada fila tiene el mismo dato, pero al filtrar solo me filtra una de las filas por lo que no puedo ver realmente todos los datos asociados a esa información.

    NÚMERO DESCRIPCIÓN CAPACIDAD MEDIO
    CUARTO

    CARROS
    MOTOS
    1 TIPO A 90 CAMIONES
    BICICLETAS
    CAMIONETAS

    y al filtrarlo solo me aparece la primera fila! como puedo solucionar esto?

    agradezco su ayuda.

  11. Buenas! Quisiera saber si hay alguna manera de hacer una lista desplegable en una celda la cual, al abrir la lista, cada dato tenga una casilla de verificación para poder marcar sólo las que queremos.
    Me explico. Necesito crear una relación de pisos que están alquilados por meses sueltos, entonces quisiera tener una lista en la que poder marcar que en el piso X la persona Y lo tuvo alquilado enero, marzo y julio....no sé si se puede hacer, la idea es que haya una lista con los meses y al desplegarla, pueda ver los meses marcados..
    Gracias!

  12. Hola...!
    Estoy creando mi primera macro... pero al ejecutarlo para probarlo en otra hoja del mismo libro me sale la siguiente ventana:
    ¡se ha producido el error 1004 en tiempo de ejecución!
    una tabla no puede superponer sobre otra tabla.

    Al dar clic en el botón depurar:

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3:$P$33"), , xlYes).Name = _
    "Tabla3"

    Espero sus comentarios
    Gracias

  13. Hola a todos, me gustaría que me puedan ayudar con el siguiente problema que a la vez es un reto que les puede servir a cualquiera algún día.

    Tengo 12 meses de venta, del cual quiero sacar el promedio de ventas de los 06 últimos meses, pero, sin considerar 4 meses cualquieras (pueden estar dentro de los 6 ultimos meses) esos meses yo lo tengo concatenado en una sóla celda.

    Gracias de antemano!

  14. Tengo un problema, creo sencillo, pero no lo puedo resolver. Tengo en una celda una lista desplegable que arme con la opcion "validacion de datos", pero cuando comparto el archivo no se porque motivo pierde la referencia. En fin, lo que quiero hacer es, y aprovechando que tengo algunas macros en la hoja, armar el listado en la celda pero con macro, pero sin utilizar una lista desplegable, quiero que se arme la lista desplegable en la celda tal como la funcion validacion de datos pero con una macro. Se puede?

  15. Hola amigos estoy en la búsqueda de alguna macro excel para comprimir (winrar ó WinZip) carpeta completa, e realizado varios intentos con algunos ejemplos vistos en Internet pero no logro hacer lo que quiero.

    ejemplo

    Sub ComprimirArchivos()
    Dim ShellApp As Object
    Dim FileNameZip As Variant
    Dim FileNames As Variant
    Dim i As Long, FileCount As Long

    ' Obtener los nombres de los archivo
    FileNames = Application.GetOpenFilename _
    (FileFilter:="Todos los archivos. (*.*),*.*", _
    FilterIndex:=1, _
    Title:=" Seleccione los archivos para comprimir. ", _
    MultiSelect:=True)

    ' Salir si se cancela el cuadro de diálogo
    If Not IsArray(FileNames) Then Exit Sub

    FileCount = UBound(FileNames)
    FileNameZip = Application.DefaultFilePath & "\PruebaComprimir.zip"

    ' Crear archivo Zip vacío...
    Open FileNameZip For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1

    Set ShellApp = CreateObject("Shell.Application")

    ' Copia los archivos a la carpeta comprimida
    For i = LBound(FileNames) To UBound(FileNames)
    ShellApp.Namespace(FileNameZip).CopyHere FileNames(i)
    Next i

    On Error Resume Next
    Do Until ShellApp.Namespace(FileNameZip).items.Count = FileCount
    Application.Wait (Now + TimeValue("0:00:01"))
    Loop

    If MsgBox(FileCount & " Los archivos se han comprimido en:" & _
    vbNewLine & FileNameZip & vbNewLine & vbNewLine & _
    " Quieres ver el archivo zip?", vbQuestion + vbYesNo) = vbYes Then _
    Shell "Explorer.exe /e," & FileNameZip, vbNormalFocus
    End Sub

    En este ejemplo me hace algo de lo que quiero pero no escoge la carpeta completa que quiero comprimir, sino que abre y hay que escoger todos los archivo, al hacerlo con un solo archivo lo encripta pero al escoger varios me da un error.

    Me ayudan por favor

  16. Hola tengo una base de datos con la cual quiero llenar una tabla pero sin tener que poner todos y solo se pongan los que elija de una lista despegable, y asi al elegir una opcion de dicha lista se rellene la tabla automaticamente.

  17. habria la posibilidad de crear una macro que concatene celdas conservando su formato (por ejemplo color, tamaño de letra, tipo, etc)

    • Las macros extienden la funcionalidad de Excel, consrvando las restricciones correspondientes a Excel, por ejemplo, si una celda contiene un color A y otra el color B, solo se podría hacer algún artificio con el format condicional para lograr que aparezcan dos colores, en cambio, si se tratan de dos tamaños de letra ese efecto si se podría realizar sin artificios de por medio.

  18. buenas noches, tengo una consulta.

    Tengo un macro para duplicar hojas a partir de una hoja "apu", y con respecto a un listado que tengo en una hoja "presupuesto", todo bien hasta ahí, lo que quiero es que al momento de insertar nuevos registros en el listado y ejecute el macro se cree la hoja nueva.

    Sub apus()
    Dim HojaOrigen As Worksheet, HojaNueva As Worksheet
    Set HojaOrigen = Sheets("Presupuesto")
    Dim ultfila As Long
    ultfila = Range("A" & Rows.Count).End(xlUp).Row
    'para recorrer los 11 registros del listado
    For i = 1 To ultfila - 1
    'desactivamos la actualización/refresco de pantalla
    Application.ScreenUpdating = False
    'duplicamos la Hoja 'modelo'
    Sheets("APU").Copy after:=Worksheets(Worksheets.Count)
    Set HojaNueva = Sheets(Worksheets.Count)
    NombreHoja = Worksheets("Presupuesto").Cells(6 + i, 1).Value
    'damos nombre a la hoja con el valor de la celda D7+i
    On Error GoTo ErrorHandler
    HojaNueva.Name = NombreHoja
    'activamos la actualización/refresco de pantalla
    Application.ScreenUpdating = True
    'trasladamos el valor de la celda item
    HojaOrigen.Cells(6 + i, 1).Copy
    HojaNueva.Cells(6, 2).PasteSpecial Paste:=xlValues
    'Seleccionas el rango q quieres combinar
    HojaNueva.Range("B6:E6").Merge
    'trasladamos el valor de la celda descripcion
    HojaOrigen.Cells(6 + i, 2).Copy
    HojaNueva.Cells(7, 2).PasteSpecial Paste:=xlValues
    'Seleccionas el rango q quieres combinar
    HojaNueva.Range("B7:E7").Merge
    'trasladamos el valor de la celda unidad
    HojaOrigen.Cells(6 + i, 3).Copy
    HojaNueva.Cells(8, 2).PasteSpecial Paste:=xlValues
    'Seleccionas el rango q quieres combinar
    HojaNueva.Range("B8:E8").Merge
    ''trasladamos el valor de la celda Cantidad
    HojaOrigen.Cells(6 + i, 4).Copy
    HojaNueva.Cells(9, 2).PasteSpecial Paste:=xlValues
    'Seleccionas el rango q quieres combinar
    HojaNueva.Range("B9:E9").Merge
    'limpiamos el portapapeles
    Application.CutCopyMode = False
    Next i
    ErrorHandler:
    HojaNueva.Name = NombreHoja & i
    'activamos la actualización/refresco de pantalla
    Application.ScreenUpdating = True
    'limpiamos el portapapeles
    Application.CutCopyMode = False
    End Sub

  19. Hola

    Necesito realizar una validación de datos para que se pueda ingresar los siguientes numeros, dónde los primeros tres no cambian, cómo podría hacerlo. Gracias

  20. Hola
    Necesito de vuestra ayuda para poder solucionar un problema que surgió en mi proyecto.
    "Convertir el contenido de una celda en fórmula."
    Os pongo en situación: Supongamos que tenemos por un lado celdas con valores y por otro una tabla que debe actualizarse según el valor de dichas celdas.
    A1: 4 >>> [valor_1]
    A2: 2 >>> [valor_2]
    A3: ([valor_1]*[valor_2])+Tabla_1[@[importe]] >>> [valor_calculado]
    Si llevara el signo = delante devuelve un error, por ello ha de dejarse como texto
    nota: @ es [#Esta fila]

    Tabla_1.- campos:
    [secuencia] >>> (1) será un secuencial
    [importe] >>> (10) importe x
    [total] >>> [valor_calculado] resultado ha de ser 18

    Como veréis la celda A3 es una cadena de texto y lo que se quiere es que se pueda convertir dicha cadena a fórmula.

    La pregunta es, hay función o manera alguna de convertir una cadena de texto en fórmula?

    Gracias de antemano vuestra colaboración

  21. hola buenas, un gusto saludarte, mi aprecio y respeto, quiero consultar lo siguiente: tengo una hoja excel 2013 cualquiera y debo llenarla de datos y necesito de que al salir de esta hoja de datos, todas las filas con datos se bloqueen (con una clave) y cuando vuelva a entrar a esta hoja las filas con datos anteriores esten bloqueados con clave (y no puedan modificar, ni editar, si pueda sumar ordenar copiar) y se pueda continuar escribiendo en las siguientes filas datos nuevos. la hoja debiera estar protegida solo a la modificacion con una contraseña al igual que el editor del viausl basic . quedo muy agradecido de antemano gracias

  22. Hola,
    quiero invocar el Excel desde la línea de comandos, y necesitaría saber cómo pasarle la pwd cuando el libro está cifrado. Tendría que ser de manera silenciosa, es decir, que abra el libro sin desplegar mensajes ni alertas para el usuario. La línea que uso es:
    "C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE" "Mi_libro.xlsm"
    y tendría que agregarle de alguna forma la pwd.
    Gracias por la ayuda y el contenido del sitio me parece muy interesante.
    Saludos
    Alejo

  23. Hola me gustaria saber si se puede hacer una macro en excel para enviar correos masivos pero no por outlook si no por gmail...¿es posible?

  24. Tengo 8 variables numéricas con las que tengo que filtrar series de datos que contienen dichas 8 variables en cada uno de los sets de datos. Mi idea es poner checkboxes en un UserForm para seleccionar 1 de 8, 3 de 8, 6 de 8, etc. variables dando clic en los respectivos checboxes y me filtre solo los sets de datos que cumplan con un máximo y mínimo determinado para cada variable. ¿Será necesario escribir todas las posibles combinaciones entre mis 8 variables para esta macro o hay una forma más simple de realizarlo?

  25. hola amigo, necesito de tu ayuda:
    como podría hacer para que en una tabla al borrar los datos de su última fila, la tabla se redimensiones (tenga una fila menos), sin tener que hacerlo manualmente desde el ángulo (en la parte inferior derecha de la tabla).
    ni con el comando CAMBIAR TAMAÑO DE TABLA.
    desconozco si esto se puede configurar en las opciones de excel o se puede hacer una macro para tal fin.
    gracias por tu atención.
    hausser.

Deja una respuesta

Los campos requeridos estan marcados con *.