时间戳

首页 > 解决方案 > 使用 VBA 清除格式很慢，下面的代码可以以更好的方式重写

excel - 使用 VBA 清除格式很慢，下面的代码可以以更好的方式重写

问题描述

代码用于删除当前格式，删除格式基于单元格值 = 3 在列

Sub Format1()

Dim I As Long
Dim LastRow As Long
Dim lrowno As Long

Application.ScreenUpdating = False

LastRow = Range("B" & Rows.count).End(xlUp).Row


    For lrowno = 4 To LastRow  
        If Range("A" & lrowno).Value = 3 Then  
            Range("H" & lrowno, "I" & lrowno).ClearFormats        
        End If  
        lrowno = lrowno + 4 
    Next lrowno  

    MsgBox ("Report is Generated")  

End Sub

标签： excelvba

解决方案

  1. 为了提高速度，您应该将单元格读/写操作的数量减少到最低限度。因此，您可以将整个 A 列读入一个数组（以检查那里的值）并收集您想要在变量中清除格式的所有范围RangeToClear。

  2. lrowno = lrowno + 4永远不要在For循环中弄乱计数器变量。要么使用Do循环，您可以自己增加计数器，要么使用Step 5to make Nextincrease by5代替 by 1。

Option Explicit

Public Sub FormatReport()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'specify your sheet

    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    Dim ValuesOfColumnA() As Variant 'read column A into an array for faster data access!
    ValuesOfColumnA = ws.Columns(1).Value

    Dim RangeToClear As Range

    Dim iRow As Long
    For iRow = 4 To LastRow Step 5
        If ValuesOfColumnA(iRow, 1) = 3 Then 'checking the array is much faster than checking the cell!
            If RangeToClear Is Nothing Then 'first range to clear
                Set RangeToClear = ws.Range("H" & iRow, "I" & iRow)
            Else 'append/union all the other ranges to clear
                Set RangeToClear = Application.Union(RangeToClear, ws.Range("H" & iRow, "I" & iRow))
            End If
        End If
    Next iRow

    'if something to clear was found then clear
    If Not RangeToClear Is Nothing Then
        RangeToClear.ClearFormats
    End If

    MsgBox "Report is Generated"
End Sub

推荐阅读