首页 文章

需要运行两次VBA宏才能从电子表格中删除空白行

提问于
浏览 次
0

所以我一直在研究一个我从我的同事那里继承的宏 . 问题是，包括主要数据在内，创建了超过一百万个空白行 . 此工作表是从源表创建的，包含30000行 .

我是VBA菜鸟，所以我一直在仔细阅读代码，希望能够理解它 . 过去四个小时我一直在努力，但我没有取得任何进展 . 我决定创建一个单独的宏，在事后删除空白行 . 只有一个问题：我必须运行宏两次才能摆脱百万个空白行 .

第一次运行它时，黑色边框（从第一张纸上转移）被删除，留下一百万个无边界行 . 我再次运行它，留下最后一个使用过的单元格 . 我只是不明白发生了什么 . 这是我一直在使用的代码 .

Sub DeleteUnused()


Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range


For Each wks In ActiveWorkbook.Worksheets
  With wks
    myLastRow = 0
    myLastCol = 0
    Set dummyRng = .UsedRange
    On Error Resume Next
    myLastRow = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByRows).Row
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, lookat:=xlWhole, _
        searchdirection:=xlPrevious, _
        searchorder:=xlByColumns).Column
    On Error GoTo 0

    If myLastRow * myLastCol = 0 Then
        .Columns.Delete
    Else
        .Range(.Cells(myLastRow + 1, 1), _
          .Cells(.Rows.Count, 1)).EntireRow.Delete
        .Range(.Cells(1, myLastCol + 1), _
          .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
  End With
Next wks

End Sub
excel vba excel-vba

2 回答

  • 1

    根据我的评论，这将删除空白行 . 只需将此作为创建空行的宏的最后一行 .

    Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    回复于
  • 1

    这是我用来清理所有空白行和空白列的宏 .

    您可以决定是否只想删除空行，并保留空列 .

    Sub Remove_Empty_Rows_And_Columns()

    Dim wks As Worksheet

    Dim row_rng As Range   'All empty rows will be collected here
    Dim col_rng As Range   'All empty columns will be collected here

    Dim last_row As Long    'points to the last row in the used range
    Dim last_column As Long 'points to the last column in the used range

    Dim i As Long           'iterator

    Set wks = ActiveSheet

    With wks

        'finding last row in used range
        last_row = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'finding last column
        last_column = .UsedRange.Columns(.UsedRange.Columns.Count).Column

        'loop through all rows in the used range and
        'find if current row is blank or not
        For i = 1 To last_row
            If Application.WorksheetFunction.CountA(.Rows(i)) = 0 Then
                'current row is blank..

                If row_rng Is Nothing Then
                    'this is the first blank row. Lets create a new range for it
                    Set row_rng = .Rows(i)
                Else
                    'this is not the first. Let's add it to the previous others
                    Set row_rng = Excel.Union(row_rng, .Rows(i))
                End If
            End If
        Next


        'same logic applies for empty rows
        For i = 1 To last_column
            If Application.WorksheetFunction.CountA(.Columns(i)) = 0 Then
                If col_rng Is Nothing Then
                    Set col_rng = .Columns(i)
                Else
                    Set col_rng = Excel.Union(col_rng, .Columns(i))
                End If
            End If

        Next

    End With


    'lets check if we managed to find any blank rows
    If Not row_rng Is Nothing Then
        row_rng.EntireRow.Delete
    Else
        MsgBox "no rows to delete"
    End If


    'checking if we found any empty columns
    If Not col_rng Is Nothing Then
       col_rng.EntireColumn.Delete
    Else
        MsgBox "no columns to delete"
    End If


End Sub
    回复于

相关问题