Trying to empty cells to plot gaps in chart

Question

I have a series of data, running from cells C169:N174, sourcing a line chart. Cell data is either a number, or empty (Excel empty) - the formula returns either the number or blank text ("").

When the action is triggered, via data validation, I need the formulas in the right-most cells (N169:N174) copied left through C169:C174). To accomplish this, I have this code:

Range("N169:N174").AutoFill Destination:=Range("C169:N174")
Range("C169:N174").Select

After the formulas are copied, I need any cell containing text (""), to be cleared. To accomplish this, I have this code:

Range("C169:M174").SpecialCells(xlCellTypeFormulas, 2).Select
Selection.ClearContents

This code runs for two sets of data. Same code, different cells. Ranges are C169:N174 and C145:N150. I have the code written for range1 (C145:N15) first - formulas copy then cells w/ ("") deleted. Then same action for range2 (C169:N174). Full code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

If Target.Address = "$B$2" Then

'mcroCopyTrendedRentFormulas
    Range("N145:N150").AutoFill Destination:=Range("C145:N150"), Type:=xlFillDefault
    Range("C145:N150").Select

    'mcroClearTrendedRentEmptyCells
    Range("C145:N150").SpecialCells(xlCellTypeFormulas, 2).Select
    Selection.ClearContents

    'mcroCopyAskingRentFormulas
    Range("N169:N174").AutoFill Destination:=Range("C169:M174")
    Range("C169:N174").Select

    'mcroClearAskingRentEmptyCells
    Range("C169:M174").SpecialCells(xlCellTypeFormulas, 2).Select
    Selection.ClearContents

    Range("A1").Select

End If

End Sub

Once the code is triggered, if I click anywhere on the sheet before it has finished running, which I just timed and it took 45 seconds, I get an error saying either:

"1004 Error: No cells were found"

or

"1004 Error: AutoFill method of Range class failed".

If I let the code run the entire 45 seconds, it works. If I click anywhere in the sheet and get either error, stop the debugger and try to run it again, I get one of the two errors.

So maybe speeding up execution is the issue?

I don't know - open to anything here.

I'll keep looking at the rest, but real qucik, you should turn on ScreenUpdating at the end of your `Worksheet_Change`, or move it inside the `If` Statement. As you have it now, it'll turn off whenever there's a change in the worksheet, and never turn back on. (PS: nicely asked first question!) — BruceWayne, Nov 13 '15 at 22:10
Why `click anywhere` when the code is running? Why not just let it run? — findwindow, Nov 13 '15 at 22:11
Try setting `Application.EnableEvents` to `False` at the beginning of your code, then setting it to `True` at the end. — Kyle, Nov 13 '15 at 22:20

BruceWayne · Accepted Answer · 2015-11-13T22:25:41.027

0

I slightly edited the code, mainly removing superfluous .Select lines (well, commented them out) and combined .Select and .ClearContents. This should run a little faster. Note the use of ScreenUpdating and Calculation.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$2" Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

    'mcroCopyTrendedRentFormulas
    Range("N145:N150").AutoFill Destination:=Range("C145:N150"), Type:=xlFillDefault
    'Range("C145:N150").Select

    'mcroClearTrendedRentEmptyCells
    Range("C145:N150").SpecialCells(xlCellTypeFormulas).ClearContents

    'mcroCopyAskingRentFormulas
    Range("N169:N174").AutoFill Destination:=Range("C169:M174")
    'Range("C169:N174").Select

    'mcroClearAskingRentEmptyCells
    Range("C169:M174").SpecialCells(xlCellTypeFormulas).ClearContents

    Range("A1").Select

End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

I also removed the 2 from your SpecialCells() as I don't believe that's required, and was throwing an error for me as well until I removed it.

While this is running, let it run without clicking. Really while any Macro is running in Excel, it's best to just let it sit and run.

edited Nov 13 '15 at 22:25

answered Nov 13 '15 at 22:17

BruceWayne

22,923
15
65
110

Good stuff - much appreciated. Not sure why (maybe the .select change) but after this the code executes much faster. A few seconds v. half a minute plus earlier. – SMF Nov 13 '15 at 22:27
And why the clicking - was a function of the time running. When it runs in a few seconds, no need to click. But when it was talking 30-45 seconds, different story. – SMF Nov 13 '15 at 22:28
[Avoiding `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) is a best practice for VBA, it does slow it down and also leaves room open for VB to do not quite what you were wanting. Also, the `ScreenUpdating`, `Calculation`, and `EnableEvents` should speed it up too. Also, is there a need to click in Excel when it's taking some time to run? You can work in other programs, but Excel tends to not like it when you bother it (i.e. click) while running macros. – BruceWayne Nov 13 '15 at 22:29
Interesting - good to know about .select. It does seem unnecessary given the ability to combine into one line. Very much appreciate the help - smf – SMF Nov 13 '15 at 22:33

Trying to empty cells to plot gaps in chart

1 Answers1