Clearing the contents of non blanks in a dynamic range

2vao

New Member
Joined
Feb 11, 2011
Messages
10
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,583
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Feb 11, 2011
Messages
10
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,583
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
Range("B11:B" & 11 + [D10])
 

Watch MrExcel Video

Forum statistics

Threads
1,133,616
Messages
5,659,866
Members
418,535
Latest member
Ajith55

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top