SOLVED

clear cells after a certain row

%3CLINGO-SUB%20id%3D%22lingo-sub-2545487%22%20slang%3D%22en-US%22%3Eclear%20cells%20after%20a%20certain%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2545487%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20am%20trying%20to%20write%20a%20macro%20where%20it%20reads%20in%20a%20number%20from%20the%20excel%20sheet%20and%20after%20clears%20all%20rows%20in%20the%20sheet%20after%20that%20number.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20excel%20below%20it%20is%207.%20so%20in%20the%20after%20all%20rows%20after%207%20is%20cleared.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20number%20may%20be%20subject%20to%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20my%20code%20looks%20like%20right%20now.%20Where%20I%20am%20having%20trouble%20is%20defining%20how%20to%20put%20the%20number%20into%20the%20reference%20of%20where%20to%20start%20the%20clear.%20I%20want%20it%20to%20say%20A%22reference%20of%20number%22%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESub%20Clearcells()%3CBR%20%2F%3E'Clear%20Report%20to%20only%20needed%20cells%3C%2FP%3E%3CP%3Ewhatsinthecell%20%3D%20Worksheets(%22Before%22).Range(%22Q5%22).Value%3C%2FP%3E%3CP%3ERange(%22Awhatsinthecell%22%2C%20%22A1000000%22).Clear%20%3CFONT%20color%3D%22%2300FF00%22%3E*does%20not%20work%20no%20clear%3C%2FFONT%3E%3CBR%20%2F%3ERange(%22Bwhatsintecell%22%2C%20%22B100000%22).Clear%3CBR%20%2F%3ERange(%22B2%22%2C%20%22B1000000%22).Clear%20%3CFONT%20color%3D%22%2300FF00%22%3E*works%20clears%20cell%3C%2FFONT%3E%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EDan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2545487%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2546119%22%20slang%3D%22en-US%22%3ERe%3A%20clear%20cells%20after%20a%20certain%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2546119%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1102909%22%20target%3D%22_blank%22%3E%40drw0ng%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20DeleteTest()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20r%20%3D%20Val(Range(%22Q5%22).Value)%0A%20%20%20%20If%20r%20%26lt%3B%200%20Then%0A%20%20%20%20%20%20%20%20Beep%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20r%20%2B%201%20%26amp%3B%20%22%3AI1048576%22).Clear%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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

 

Thanks,

Dan

2 Replies
best response confirmed by drw0ng (New Contributor)
Solution

@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
Thank you this worked perfectly!

I really appreciate your help Hans