Range Fill Down Method

  • Hi,

    The following statement seems to work in the immediate window but not as a part of a macro attached to a command button.

    Range("P9:AA" & Range("length").Value + 7).FillDown

    Any idea what is wrong with it?


  • Hmmm. I'm using Xl97 on NT. I created a new workbook, defined a cell as length and put a number in it. I put some data in P9:AA9. I then pasted your one liner into a new macro and ran it. Data filled down OK. Can you attach your problem workbook?

  • One solution to your problem.

    An example: Say you have data/formula in cells P1 to AA1 and you want to fill downwards to the last cell. Then

    a = Range("A65536").End(xlUp).Row ''(Assuming column A has a full length of data)

    Selection.AutoFill Destination:=Range("P1:AA" & a)

    This should solve your problem.



  • Weird. I experienced the same problem. Apparently there is something about filldown being invoked by a command button that doesn't work. I even modified the commandButton1_Click to call a sub in a general module to fdo the fill down. Same error. If you attach the macro (from a general module now) to a Form's button, all runs as expected. I guess that is what you will need to do.

    I leave it to more knowledgable folk to explain this apparent anomoly.

  • Hi guys, sounds like the xl97 TakeFocusOnClick bug.

    Try setting the TakeFocusOnClick Property to False of your CommandButton.

    Or use Activecell.Activate as the 1st line in the code.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!