vba - 加速 VBA 宏

Question

我有一个可以工作的宏，当有很多数据时它真的很慢，我希望这里有人可以帮助我加快速度。

当我的 VBA 做的是检查工作表的列是否有值“NULL”，如果它在那里，它会清除该单元格。这是代码:

Sub RemoveNullColumn()
    Dim c, count, r, lc, FirstCell
    Application.ScreenUpdating = False
    count = 0
    r = ActiveCell.row      'lets you choose where you want to start even if it is not at "A1"
    c = ActiveCell.Column   'lets you choose where you want to start even if it is not at "A1"
    c = GetLetterFromNumber(c)  'Gets the column letter from the number provided above
    FirstCell = c & r       'sets the cell that you selected to start in so that you will end thereafter removing all the NULL

    lc = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column   'Finding the last used column
    For H = ActiveCell.Column To lc Step 1      'Starts with where you selected a cell and moves right to the last column
        For x = 1 To Range(c & Rows.count).End(xlUp).row Step 1     'Starts with the first row and moves through the last row
            count = count + 1
            If Range(c & x).Value = "NULL" Then 'Checks the contents fo the cell to see if it is "NULL"
                Range(c & x).Clear
            End If
            If count = 1000 Then    'This was used testing but is not seen with the ScreenUpdating set to false
                Range(c & x).Select
                count = 1
            End If
        Next x
        ActiveCell.Offset(0, 1).Select  'select the next column
        c = ActiveCell.Column
        c = GetLetterFromNumber(c)      'get the letter of the next column
    Next H
    Application.ScreenUpdating = True
    MsgBox "Finished"
    Range(FirstCell).Select
End Sub

Function GetLetterFromNumber(Number)
    GetLetterFromNumber = Split(Cells(1, Number).Address(True, False), "$")(0)
End Function

当行数不多时，它非常快，但是行数很多时，它很慢。

我有一个运行它的文件，其中包含从 A 到 AD 的列和 61k+ 行，完成它花了 30 多分钟，我希望能做得更快。

Answer 1

无需查看工作表中的每个单元格，而是使用速度更快的替换功能:(您可能需要根据需要对其进行编辑)

例子 :

Sub RemoveNullColumn()

  Dim targetSheet As Worksheet
  Set targetSheet = ActiveSheet 'TODO: replace with a stronger object reference

  targetSheet.Cells.Replace What:="NULL", Replacement:="", LookAt:=xlWhole, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
   ReplaceFormat:=False
End Sub

这将确保您将保留格式。