Forum Discussion

drw0ng's avatar
drw0ng
Copper Contributor
Jul 14, 2021
Solved

clear cells after a certain row

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

 

Thanks,

Dan

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 14, 2021

    drw0ng 

    Try this:

    Sub DeleteTest()
    Dim r As Long
    r = Val(Range("Q5").Value)
    If r < 0 Then
        Beep
    Else
        Range("A" & r + 1 & ":I1048576").Clear
    End If
End Sub

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 14, 2021

    drw0ng 

    Try this:

    Sub DeleteTest()
    Dim r As Long
    r = Val(Range("Q5").Value)
    If r < 0 Then
        Beep
    Else
        Range("A" & r + 1 & ":I1048576").Clear
    End If
End Sub
    • drw0ng's avatar
      drw0ng
      Copper Contributor
      Jul 14, 2021
      Thank you this worked perfectly!

      I really appreciate your help Hans 🙂

Resources