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