宏 - 删除工作簿中多个工作表上的空白列（具有标题）-解网

问：

请为相对新手提供帮助.....我正在寻找一种方法来删除单个工作簿中多个工作表中的所有空白列（有标题）。

我有一个代码（我没有写），可以删除活动工作表中的所有空白列，这工作正常。但是，我想要一种让此代码同时在多个工作表中工作的方法。我找不到任何东西：

Sub DeleteBlankColumns()
Dim i As Long
    Dim lnglastcolumn As Long
    Dim delrng As Range
    
    With ActiveSheet
        lnglastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
        
        For i = 1 To lnglastcolumn
            If .Cells(.Rows.Count, i).End(xlUp).row = 1 Then
                If delrng Is Nothing Then
                    Set delrng = .Columns(i)
                Else
                    Set delrng = Union(delrng, .Columns(i))
                End If
            End If
        Next i
    End With
    
    delrng.Delete
End Sub

任何帮助非常感谢！

Excel VBA

Sub DeleteBlankColumns(curSheet As Worksheet)
    Dim i As Long
    Dim lnglastcolumn As Long
    Dim delrng As Range
    
    With curSheet
        lnglastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
        
        For i = 1 To lnglastcolumn
            If .Cells(.Rows.Count, i).End(xlUp).Row = 1 Then
                If delrng Is Nothing Then
                    Set delrng = .Columns(i)
                Else
                    Set delrng = Union(delrng, .Columns(i))
                End If
            End If
        Next i
    End With
    
    If Not (delrng Is Nothing) Then
        delrng.Delete
    End If
End Sub

Sub Run()
    'DeleteBlankColumns Sheets("Sheet1")
    'DeleteBlankColumns Sheets("Sheet2")
    For Each sh In ActiveWorkbook.Worksheets
        DeleteBlankColumns Sheets(sh.Name)
    Next sh
End Sub

Sub DeleteEmptyColsInAllSheets()
 Dim wb As Workbook, sh As Worksheet, delRng As Range, lnglastcolumn As Long, i As Long
 Set wb = ActiveWorkbook ' use here the workbook you need

 For Each sh In wb.Worksheets
    With sh
        lnglastcolumn = .cells(1, Columns.count).End(xlToLeft).column
        
        For i = 1 To lnglastcolumn
            If .cells(.rows.count, i).End(xlUp).Row = 1 Then
                If delRng Is Nothing Then
                    Set delRng = .Columns(i)
                Else
                    Set delRng = Union(delRng, .Columns(i))
                End If
            End If
        Next i
    End With
    If Not delRng Is Nothing then
        delRng.Delete: Set delRng = Nothing
    End If
 Next sh
End Sub

宏 - 删除工作簿中多个工作表上的空白列（具有标题）

Macro - Delete blank columns (which have headers) on multiple worksheets within workbook

评论

评论

评论