Selecting a variable range of cells

[wadeer]

I have developed a spreadsheet for a golf outing with a maximum number of players set at 28 in Excel 2003. Usually we end up with less than 28 but not always. When I run a macro which copies formulas to the spreadsheet it does so for all 28 rows. I need to select the number of rows within the 28 rows that needs the formulas erased. Cell A31 contains the number of rows that needs to be erased. The columns that contain the formulas are U-AA. Thanks in advance.

 

[MARK858]

Personally I would get the original macro to only fill in the required rows but try the code below on a test sheet.

Sub eraseRws()
    Dim i As Long
    i = Range("A31").Value
    Intersect(Rows(28).Offset(-(i - 1)).Resize(i), Columns("U:AA"))ClearContents
End Sub

 

[wadeer]

Thanks Mark858. I keyed in as you suggested but received an error message box when I finished with ClearContents. The message stated "Compile Error: Expected: End of Statement". Any suggestions?

 

[MARK858]

Sub eraseRws()
    Dim i As Long
    i = Range("A31").Value
    Intersect(Rows(28).Offset(-(i - 1)).Resize(i), Columns("U:AA"))[COLOR="#FF0000"][B][SIZE=3].[/SIZE][/B][/COLOR]ClearContents
End Sub

although it should really test for A31 being above 0

Sub eraseRws()
    Dim i As Long
    i = Range("A31").Value
    If i > 0 Then Intersect(Rows(28).Offset(-(i - 1)).Resize(i), Columns("U:AA")).ClearContents
End Sub

 

[wadeer]

Added the additional code to the beginning of the line and it still errors out. Sorry for being a pest.

 

[MARK858]

Copy and paste the code, don't add it as the correction was in the first code as well and you haven't spotted it.

 

[wadeer]

Thank You works as designed. Appreciate your work. Ed

 

[MARK858]

You're welcome