xltoday.net

EXCEL 2007 TABLAS Y GRAFICOS


Columnas de Excel y VBA

Presentamos unas macros para trabajar con columnas Excel desde Excel VBA.

Encontrar última columna (buscar al revés)


Sub EncontrarUltimaColumna() Dim intUltimaCol As Integer If WorksheetFunction.CountA(Cells) > 0 Then intUltimaCol = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column MsgBox intUltimaCol End If End Sub

Encontrar última columna (en fila especificada)


Sub EncontrarUltimaColumna() Dim intUltimaCol As Range If WorksheetFunction.CountA(Rows(1)) > 0 Then Set intUltimaCol = Range("IV1").End(xlToLeft) MsgBox intUltimaCol.Address End If End Sub

Suprimir columnas vacías en el rango


Sub SuprimirColumnas() Dim cCount As Integer, c As Integer Dim DeleteRange as Range DeleteRange =("A1:B10") If DeleteRange Is Nothing Then Exit Sub 'Ejemplo: DeleteEmptyColumns Range("A1:Z1") If DeleteRange.Areas.Count > 1 Then Exit Sub With DeleteRange cCount = .Columns.Count For c = cCount To 1 Step -1 If Application.CountA(.Columns(c)) = 0 _ Then .Columns(c).EntireColumn.Delete Next c End With End Sub

Suprimir cada n-columnas


Sub SuprimirColumnas() Dim rCount As Long, r As Long Dim DeleteRange as range DeleteRange =("A1:B10") If DeleteRange Is Nothing Then Exit Sub If DeleteRange.Areas.Count > 1 Then Exit Sub If n < 2 Then Exit Sub With DeleteRange cCount = .Columns.Count For c = n To cCount Step n - 1 .Columns(c).EntireColumn.Delete Next c End With End Sub