Clear contents from range and move data up

  • I have a range of cells from B3:G24 that I wish to choose what row and above to clear contents by sticking an X in column H representing the row and above between B3:G24 to clear contents. I then want the data in the range below X that could go down to B200:G200 to then be cut and and values pasted from B3:G24 down over.

    I’d like to run this from a command button.

  • Hello,


    Attaching a sample file would clarify things ....


    For example B3:G24 seems to be your reference range ... but a couple of sentences later you do mention B200:G200

    so probably row 24 is not your last row ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sorry about that, I’m not in the office.

    I will only ever need to clear the contents between B3:G24. Either a single line (B3:G3) by putting X in H3, or a couple of lines (B3:G4) by putting X in H4. I’ll only ever need to clear the contents of B3:G24, but I would have data going right down to row Between B and G to row 200.

    When I clear contents of the selected rows, whether it be the first row (B3) or a block of rows (B3:G24), I want the data below the contents cleared to move itself up.


    Hope that makes sense. 😁👍🏻

  • So ... What is actually missing in the test file ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I'll give you a bit more of an insight.

    The company I work for might receive a request for call recordings, we download all the details of the calls on to the spread sheet attached, the headers will make more sense on the attachment.

    We can burn anywhere between one call to 24 calls on to a cd, once the call is burnt on to the first cd, we would then highlight the cells to print a cd cover off, then manually clear the contents of the calls burned on to the first cd. we would then copy the remaining calls below and paste the values in b3.

    This would then refer to cd 2 and put the next lot of call recordings in place for the next burn, and so forth.


    I was hoping i could put an x in column H of the last call on the cd, which would clear the content of the range, and move the below calls up to the top ready for cd 2.


    Hope that makes sense.

  • OK ...


    Thanks for the explanation ...


    Basically what you are after ... is an automatic delete ... (since you have burned your cd...)


    Hope attached file will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Glad this could help you fix your problem :)


    Thanks a lot for your Thanks ... AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I’ve just had an after thought, is it possible to create a print command script to to include headers, column A, and the same selection dependent on where the X is placed?

  • I want to run the sub from a command button, but when I cut and paste the script from sheet1 to a module and remove private, it does not show in the macro box for me to assign to the button. Any ideas?

  • Hello,


    You are right ... an Event macro cannot be copied as it is to a standard module ...;)


    Will prepare the modifications ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • As ever you have worked your magic again... 😉👌🏻👍🏻.


    Sorry to be a pain, but on a final thought to further automate, is it possible to count the rows of call length column up to 80 minutes (maximum size allowed on cd) to then have the area printed, then the next lot of rows moved up and loop through the same until no more rows are left?

    This would then remove the need for me to use the X.

    Was just a thought.

  • Understand you would like the print area to be automatically determined by the call length ...


    Since Excel can handle everything ;) ... why not ...


    But this will require you to attach a sample file ... along with all your precise rules ...


    P.S. By the way ... in your real life worksheet ... do you have a column showing your running total of minutes ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Just logged on to office pc and pulled this off, I should of sent you this from the begining.


    The staff member would list all the call details on the spreadsheet attached, they would then press the sort button to put them in date order, then add up the call duration so it's less than 80 and put an x in column G (I thought X should of been in H sorry). They would press the amend row button to adjust the rows to a size that would make it a perfect fir for the cd case.

    After that they would highlight the rows up to the row with an X in then print.

    Next they would clear the contents of that range, highlight the remainder calls then paste the values back at the top of the list.

    Looking back i think i should of put the buttons across the top.


    If it were at all possible to have one button the would sort, amend row height at the total call duration being 80 minutes, and print that selection, to then move rows up and repeat the process. Your last file attachment works great and i could include it with this attachment on individual buttons.

    But if it were possible to do it on one button, that would be fantastic. :)

    Files

    • CD Cover.xlsm

      (38.78 kB, downloaded 58 times, last: )
  • Thanks for the template ...


    There are several things that could make your life a lot easier ...;)


    For a start, your formula in cell A3 should be =IF(B3="","",ROW()-2) and copied down ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • A week prior it was just a template without code.

    I must admit, I’m all new to VBA but keen to automate a few in house processes. I know what I’d like to do, it’s just a lot of googling and forums to help put the projects into practice. 👌🏻👍🏻
    Also only columns A:D would be printed.

  • If you do not mind ... will review your template to suggest some improvements ...


    But, most importantly, it is crucial to stick to the reality ...


    So, could you describe the sequence of the various tasks ...as they do occur in real life ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Please find below a real life pre forum post example using the attached.


    1. Manually input call information in each row.

    2. Press sort button to sort all calls by date

    3. Working down from the top of the list add up the number of calls (Rows) by call duration of each row not exceeding 80 minutes. In attached example only the first four calls can be burnt to CD1, as the fifth call would need to be put on to CD2.

    4. Put an X in G5 to represent the fourth call, then push the amend row button. This will adjust the rows to a size that would make a cd cover of four calls to be put into the Jewel case of CD1.

    5. Highlight cells A1:D6.

    6. File print, print selection.

    7. Cut out CD Cover which will then be a perfect fit, and place inside Jewel case.

    8. Clear contents of cells B3:E6

    9. Cut cells B7:D7 and paste values into B3:D3. In this scenario as only one call is left you would go straight to step 4, however if there were up to 200 calls you would go back to step 3.


    I have put dummy call info in attached sheet, for you to start from step 2. But usually we have a lot more calls than the five i have put on the attached.

    Files

    • CD Cover.xlsm

      (35.11 kB, downloaded 34 times, last: )