clear cells after a certain row

New Contributor

Hi I am trying to write a macro where it reads in a number from the excel sheet (this number may change) and after clears all rows in the sheet after that number. 


In the Excel below, the number is 7. So what I would like is for the rows after 7 to be deleted.


This is what my code looks like right now. Where I am having trouble is defining how to put the number into the reference of where to start the clear. 


How can I make the first reference in this line of code to reflect the 7? or is there a better approach

      Range("A???", "A1000000").Clear

Sub Clearcells()

whatsinthecell = Worksheets("Before").Range("Q5").Value

Range("Awhatsinthecell", "A1000000").Clear *does not work no clear
Range("B2", "B1000000").Clear *works clears cell


End Sub




2 Replies
best response confirmed by drw0ng (New Contributor)


Try this:

Sub DeleteTest()
    Dim r As Long
    r = Val(Range("Q5").Value)
    If r < 0 Then
        Range("A" & r + 1 & ":I1048576").Clear
    End If
End Sub
Thank you this worked perfectly!

I really appreciate your help Hans