CODEWITHSUNDEEP
excelvba
Tharun
Tharun

Reputation: 11

Clear Format using VBA is Slow, can the below code be rewitten in a better way

Code is used to remove current format, removing format based on cell value = 3 in column A

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

Upvotes: 1

Views: 141

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

  1. To improve speed you should reduce the amount of cell read/write actions to a minimum. Therefore you can read the whole column A into an array (to check the values there) and collect all the ranges that you want to clear format in a variable RangeToClear.

  2. Never mess with a counter variable lrowno = lrowno + 4 in a For loop. Either use a Do loop where you can increas the counter yourself or use Step 5 to make Next increase by 5 instead 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

Upvotes: 1

Related Questions