Clearing the contents of non blanks in a dynamic range

[2vao]

Hi All,

I am learning dynamic range and I have very limited knowledge on VBA. I have cell A11 (value input by users), D10 (Counts the number of rows that A11 returns), B11 (Has an array formula to be copied based on the of D10). Here are my codes ( with the big help of Joe in 2019)

Private Sub Worksheet_Change (ByVal Target As Range)
If Not Intersect(Target, Range ("A11") ) Is Nothing Then
Application.EnableEvents = False
If [D10] > 0 Then
Range ("B12:B" & 11 + [D10] ) .ClearContents 'How do I change this code to clear the contents of non blanks instead?
Range ("B11:B" & 11+ [D10]) .FillDown 'This code copies an extra row of the B11
End If
End Sub

Thank you for your assistance

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A11")) Is Nothing Then
    Application.EnableEvents = False
    If [D10] > 0 Then
        Range("B12:B1000").ClearContents
        Range("B11:B" & 10 + [D3]).FillDown
    End If
    Application.EnableEvents = True
End If
End Sub

 

[2vao]

Thank you Fluff for responding,

1. The clearing of the contents worked, but I did not want to limit the range. I just want the code to check "*" and clear them before the code to copy B11 runs.
2. The .FillDown line code deleted B11 (B11 had the formula to copy down), I tried change 10+[D10] to 11 + ([D10]-1) but it still deleted B11

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A11")) Is Nothing Then
Application.EnableEvents = False
If [D10] > 0 Then
Range("B12:B1000").ClearContents 'This works but I don't want to limit the range, need a code that would clear previous records
Range("B11:B" & 10 + [D10]).FillDown 'This one deleted the formula in B11 so nothing to copy.
End If
Application.EnableEvents = True
End If
End Sub

 

[Fluff]

As long as you have no data below B11 that needs to be kept just change the B12:B1000 to any row below where the formula will ever be.
To prevent the formula being removed use

Range("B11:B" & 11 + [D10])