Jul 13 2021 10:32 PM - edited Jul 13 2021 11:57 PM
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
Jul 14 2021 03:10 AM
SolutionTry 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
Jul 14 2021 08:06 AM
Jul 14 2021 03:10 AM
SolutionTry 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