VBA to apply formatting when number of rows changes

[greykitten]

Hello Forum,
I'm very new to VBA and struggling with how I alter a macro to apply formatting when the number of rows changes from month to month. I used the macro recorder on a very simple example and I found the following snippet below to find the last row. But I'm not following where I add it to the code so that the macro works when the number of rows change.

Dim lRow As Long

lRow = Cells(Rows.Count, 1).End(xlUp).Row

Would it help to add this to the range?

Dim DataRange As Range

Set DataRange = Range("A1:AR" & lRow)

In the example attached, the InitialTest tab has the macro applied to it without adding additional rows. On the FailedTest tab, I added 40 additional rows to test whether the macro would determine the total number of rows and adjust the range correctly to format all of the rows. The macro stopped at row 15 which is the number of rows in the RawData tab. Can someone please help me figure out how to adjust this macro so that it can handle varying numbers of rows? Thank you very much in advance for your help.

Thanks again,
greykitten

 

[Hui]

Change your code as shown below:

It is only the few lines at the bottom that were in error

Sub MacroCVTest()
'
' MacroCVTest Macro
'
Dim lRow As Long

lRow = Cells(Rows.Count, 1).End(xlUp).Row
'
  Range("A1").Select
  Range(Selection, Selection.End(xlToRight)).Select
  With Selection.Interior
  .Pattern = xlSolid
  .PatternColorIndex = xlAutomatic
  .Color = 5296274
  .TintAndShade = 0
  .PatternTintAndShade = 0
  End With
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  With Selection.Borders(xlEdgeLeft)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlEdgeBottom)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlInsideVertical)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  With Selection.Borders(xlInsideHorizontal)
  .LineStyle = xlContinuous
  .ColorIndex = 0
  .TintAndShade = 0
  .Weight = xlThin
  End With
  Range("F2:G" & lRow).NumberFormat = "$#,##0.00"
  Range("Q2:AR" & lRow).Style = "Currency"

End Sub

 

[greykitten]

Thank you so much for your quick response Hui! That was very helpful

~ greykitten