vba跨工作簿删除空白行、空白列的问题

[zhimayan]

大神，我想在一个工作簿中，通过vba删除另一个工作簿中工作表的空行、空列
代码如下：

Windows("1.xlsx").Activate
Dim LastRow As Long, r As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1


For r = LastRow To 1 Step -1
If WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r


Dim LastColumn As Long, c As Long
LastColumn = ActiveSheet.UsedRange.Columns.Count
LastColumn = LastColumn + ActiveSheet.UsedRange.Column
For c = LastColumn To 1 Step -1
If WorksheetFunction.CountA(Columns(c)) = 0 Then Columns(c).Delete
Next c

但是总是删除vba所在工作簿工作表中的空行、空列。这个需要怎么改呢？

[milaoshu1020]

因为你的Rows和Columns没有加上ActiveSheet前缀.
修改后的代码如下:

Sub test()
    Windows("1.xlsx").Activate
    Dim LastRow As Long, r As Long
    LastRow = ActiveSheet.UsedRange.Rows.Count
    LastRow = LastRow + ActiveSheet.UsedRange.Row - 1
     
     
    For r = LastRow To 1 Step -1
        If WorksheetFunction.CountA(ActiveSheet.Rows(r)) = 0 Then ActiveSheet.Rows(r).Delete
    Next r
     
    Dim LastColumn As Long, c As Long
    LastColumn = ActiveSheet.UsedRange.Columns.Count
    LastColumn = LastColumn + ActiveSheet.UsedRange.Column
    For c = LastColumn To 1 Step -1
        If WorksheetFunction.CountA(ActiveSheet.Columns(c)) = 0 Then ActiveSheet.Columns(c).Delete
    Next c
End Sub

下载地址:
链接：https://pan.baidu.com/s/1yB-R9KTzggYU5beav8IllA