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