Ejecutar varias combinaciones de celdas con VBA Excel.
Qué código puede permitir las combinaciones de celdas en una sola acción, sin tener que repetir varias veces la misma instrucción a cada celda. A continuación le explico cómo es el desarrollo.
A través del commandButton1 ejecutar las siguientes instrucciones:
1. Combinar celdas en las columnas A, B, C y D a partir de la fila 4 . Esta combinación de celdas la llamaremos celda A.
2. Combinar celdas en las columnas E, F, G y H en la misma fila 4 . Esta combinación de celda la llamaremos celda B.
3. Darle bordes a las celdas combinadas A y B.
4. Son 40 filas para las celdas combinadas antes mencionadas.
5. Las filas 4, 9, 18 de la celda combinada A son encabezados por lo que deseo darle un tipo de fuente Arial Narrow, estilo negrita, tamaño 14 y que estén alineadas a la izquierda y centradas.
6. La altura de celda de los encabezados (Alto de fila) sean de 20, precisando que la fuente encaje bien en la celda.
2 Respuestas
Esta simple macro es grabada y hace lo que tu quieres hacer
Sub MacroGrabada()
Application.ScreenUpdating = False
Range("A4:D43").Select
Selection.Merge True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("E4:H43").Select
Selection.Merge True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("4:4,9:9,18:18").Select
Range("A18").Activate
Selection.RowHeight = 20
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial Narrow"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Size = 12
Selection.Font.Size = 14
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("A4:D4").Select
Application.ScreenUpdating = True
End SubSaludos no olvides comentar si te sirvio y valorar la respuesta.
Te anexo la macro
Sub Combinaciones_Celdas()
'Por Dante Amor
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Range("A4:D41, E4:H41").Merge True
Range("A4:H41").Borders.LineStyle = xlContinuous
'
With Range("A4:D4,E4:H4,A9:D9,E9:H9,A18:D18,E18:H18")
.RowHeight = 20
.Font.Size = 14
.Font.Bold = True
.Font.Name = "Arial Narrow"
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
End With
End SubCambia H41 si necesitas más filas o menos filas.
[Si te ayudó la información, no olvides valorar la respuesta.