Macro to clear out all cells

  • I would like a Macro to clear out all the data on my sheet and replace it with new data from the next row(s)up. My sheet is linked to 4 external books and will be getting data from 18 sheets per book. How can this be done? This data I need to link from external books are cells CDEF NOPQ XYZ AA 3:35. I do not have any issues linking or anything else only with clearing out the data and placing the new data 1 row up from the external sheets and the other data comes from the "Drawn Numbers" tab within this same book. I know I can do it by copy and paste and also write a hard formula but would like a Macro (I am not familiar enough to do myself) so I can just copy and paste into my other books.


    Thanks Gurus!!!


    http://forum.chandoo.org/threa…ste-new-row-s-data.17612/

  • Re: Help writing a Macro to clear out all cells


    If you need an external book too I can provide one but I would think as long as you had the input name or just leave that part blank for me to fill in is fine. One book is called (SSD (Z:) EXCEL SHEETS........... They are all linked to a book in this drive

  • Re: Help writing a Macro to clear out all cells


    At the moment all those cells are linked to cells on row 13 of 18 other sheets. Do you mean you want to change the link to row 12 of the linked sheets when you click the button, then 11 on the next click and so on?


    If so, then what is the minimum and maximum row number? The links will increment down one row per button click until the minimum row number is reached at which point the link will go back to the maximum (starting) row number. Is that what you want?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Help writing a Macro to clear out all cells


    Thanks for replying Kj!!! (-:


    I want to link to 3000 rows in all. The lowest will be 100 and the highest 3000. You are correct they will increment down. I would like the option to start the linking on any row after 100. I may have in one book all the data up to row 1054 and on another it may be 352. So, I may need two buttons I am not really sure YOU are the MASTER and know BEST how to do this.


    I am not certain what you mean by this: "The links will increment down one row per button click until the minimum row number is reached at which point the link will go back to the maximum (starting) row number"


    Every time I enter a new Drawn Number in the Input tab say it goes in Row 1949 IJKLMN. The sheet attached Game #40 increments to Game #41 and all the data in the cells change from rows 1948 to data in rows 1949 and so on. There are already programs in these rows that will place the new counter numbers and data to the new game sheet once tied together.


    Do you need to know which cells goes exactly to and from where? If so, I can do that really quickly?


    Again, THANK YOU!!!

  • Re: Help writing a Macro to clear out all cells


    (There is a hidden formula in A8, A:16, A:24 (L and W) foranother book) these are all in the blue sections)

    A3: A8 Drawn Numbers tab F Q AB AM AX BI
    A11:A16 DrawnNumbers tab C N Y AJ AU BF
    A19:A24 DrawnNumbers tab I T AEAP BA BL
    A29:A35 DrawnNumbers tab BI:29 BF:32 BL:35
    The same tabs above also go to the same Drawn Numbers tabfor columns L and W
    L3: L8 DrawnNumbers tab F Q ABAM AX BI
    L11:L16 DrawnNumbers tab C N Y AJ AU BF
    L19:L24 Drawn Numbers tab I T AE AP BA BL
    L29:L35 Drawn Numbers tab BI:29 BF:32 BL:35


    W3: W8 DrawnNumbers tab F Q ABAM AX BI
    W11:W16 DrawnNumbers tab C N Y AJ AU BF
    W19:W24 DrawnNumbers tab I T AEAP BA BL
    W29:W35 DrawnNumbers tab BI:29 BF:32 BL:35


    The counters (CDEF NOPQ XYZ AA AB) come from each externalbook.
    R3:R8 Numbers Book
    R11:R16 50+ Hit Numbers Book
    R19:R24 OB Hits Numbers Book
    R:29 NumbersBook
    R:32 50+Hit Numbers Book
    R:35 OB Hits Numbers Book


    GH RS AC AD comes from the same Game# sheet ref columns androws
    Not sure if you needed this but if you did, hope this helps

  • Re: Help writing a Macro to clear out all cells


    As per usual your explanation leaves me more confused than when I started!!


    Every relevant cell in the sample workbook you attached is linked to a cell in row 13 of various sheets in another workbook. Now you say it will be from row 3000 to row 100, ok that is fine.


    You have a button there with the text 'clear' but no macro assigned to it. If you clear the relevant cells you will remove the linking formula. If you just change the formula so that the link changes to one row less than previously then the value of each cell will change accordingly.


    What I suggested was to assign a macro to the button which will, when the button is clicked, change the formula in each relevant cell to link to the same workbook, sheet and column but one row less. This would happen each time the button is clicked until row 101 is reached (the minimum will have to be 101 not 100) then the macro will go back to what ever maximum you set in the code. But this would only work if all linking rows had the same starting row.


    However, you now say that the staring row will not be the same for every linked cell. In that case the macro will still work to link to one row less for each cell every time the button is clicked. But, when the first cell has it's linked row reduced to 100 you will get a message to say that the minimum row has been reached for that cell and the macro will exit. You will then have to manually reset the linking formulas ready to start again.


    I hope that makes sense.



    Other formulas will not be affected, only formulas that start with: ='F

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Macro to clear out all cells


    I have no intentions on confusing you are anyone so my apologies. I thought by just uploading a single sheet would be best since some do not like downloading from links. Here is the main Input book I am referencing too. As always, I THANK YOU, you are AMAZING and put the Excel (surpass) in Excel!! The clear on the game sheet is to clear out the letters in IJ TU AE AF


    https://app.box.com/s/9z1lrbqr77yfpkg8r0bi

  • Re: Macro to clear out all cells


    When I say the starting row will be different I am talking about each "set" of books may start at a different row. If I have 5 "different sets" of books lets say ABCDE A may start at row 1054 and all associated external books will also start at 1054 with in the set of A, B may start at row 352 and all the external books associated with B will all start at the same row. I just wanted to ability of taking the same Macro and be able to place them in any book. Hope that clarifies things

  • Re: Macro to clear out all cells


    Here is the code I tried to get to run and did not leave it in the book but for some reason it is not working either. Perhaps someone can critique it


Participate now!

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