CODEWITHSUNDEEP
excelvba
Vaggelis
Vaggelis

Reputation: 57

VBA dynamic cell range resize

I have written the following code and I want to format the Grand Total row. I want to make this dynamic because the cells of the grand total row change every month 

Sub ReportData()

Application.ScreenUpdating = False

Dim Report As Worksheet
Set Report = WBNew.Worksheets("Report")

Report.UsedRange.Copy
With Sheets("Report").UsedRange
     .PasteSpecial xlPasteFormats
     .PasteSpecial xlPasteValues
     .Columns("A:Q").EntireColumn.Delete
     Set Rng = .FIND(What:="Grand Total", LookAt:=xlWhole, LookIn:=xlValues)
     Rng.Resize(, 18).Interior.ColorIndex = 20
End With

Application.ScreenUpdating = True

End Sub

For example if I find "Grand Total" in cell A20, then I want to extent to the last used cell for example X20. But this is my problem it's not always X20 it changes.

Upvotes: 0

Views: 166

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Use .Cells(Rng.Row, .Columns.Count).End(xlToLeft) to find the last used cell in the row where you found Grand Total and use that to color the range.

Set Rng = .FIND(What:="Grand Total", LookAt:=xlWhole, LookIn:=xlValues)
If Not Rng Is Nothing Then
    With Sheets("Report")
        .Range(Rng, .Cells(Rng.Row, .Columns.Count).End(xlToLeft)).Interior.ColorIndex = 20
    End With
Else
    MsgBox "Grand Total was not found."
End If

Note that you need to check If Not Rng Is Nothing Then otherwise you will run into an error if Grand Total does not exist.

Upvotes: 1

Related Questions