如何删除空白列VBA

Question

我在Excel中导入数据在我的表中修复了95列我想创建一个宏或代码，只要我将数据粘贴到Excel中，如果整个列完全空白，那么它应该被删除。

Answer 1

你可以试试这个：

Sub Main 
    Dim iCol As Long 

    With Worksheets("mySheetName").UsedRange '<--| change "mySheetName" to your actual sheet name 
     For iCol = .Columns.Count to 1 Step - 1 
      If WorksheetFunction.CountA(.Columns(iCol)) = 1 Then .Columns(iCol).EntireColumn.Delete 
     Next 
    End With 
End Sub 

Answer 2

Dim r As Range, rows As Long, i As Long 
Set r = ActiveSheet.Range(Cells(1, 1), Cells(lastrow, lastcolumn)) 
rows = r.rows.Count 
For i = rows To 1 Step (-1) 
If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete 
Next