Autofill formula to a number of rows specified by value(changing) in another cell

[NBhinder]

Hi,

I am a beginner at writing macros. I am trying to autofill formula in A1 to A("value mentioned in cell B1). FillDown code works alright if the changing value in cell B1 is going up. E.g.: if B1= 15 my formula gets filled to A15 but next time if B1=10, it leaves the previous values in cells A11 to A15 (ideally these values should disappear for me). I tried second part of the code but there is wrong with it; please help.

Here is my code:
Private Sub CommandButton1_Click()
Range("a1:a" & Range("B1").Value).FillDown


Range("A & Range("B1").value +1: a100").Formula = ""
End Sub



Also, is there a way to achieve this directly in Excel without a code?

Many thanks,
Navneet

 

[JoeMo]

Try this:

Private Sub CommandButton1_Click()
Range("A2:A" & Cells(rows.count,"A").End(xlUp).Row).ClearContents  'Removes any prior fill down
Range("a1:a" & Range("B1").Value).FillDown
End Sub

 

[NBhinder]

Thank you for help Joe, this is great works perfectly.

Regards,
Navneet