1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: XLS Macro to delete specific rows/columns and move columns to rows.

Discussion in 'Business Applications' started by angelostudios, Oct 12, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    I have been working on a project for a few weeks and I could really use some help. I'm attaching a dummy file (example1) that is an example of how my XLS comes to me each month.

    I need a macro that leaves me with the second file (example2). If the macro could just grab the Student name, then put the following info for each student all on 1 row, that would be perfect:

    Child's Name | Date In | Time In | Rounded | Time Out | Rounded

    Sometimes for some reason there is a student with a (Cont) after their name. I need that row deleted and just pull the times up onto the same row as other times. The example2 file might make sense.

    Here are the steps I am doing manually that I hope a macro could do for me!

    1. Un-merge cells: B10
    2. Un-merge any cells with a child's name such as Doe, Jane
    3. Delete any row with a childs name that has (Cont) after it such as Doe, Jane (Cont)
    4. Delete rows: 1-9
    5. Delete columns: A, C, F, H, I, J, M, N, O, and P
    6. Manually move all info up to one row per student
    7. Delete all empty rows below
    See example2 as the completed file but I only moved a few of the rows per student for the sake of time.

    Is there any way to do all of this with a macro?
     

    Attached Files:

  2. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    This is very doable through VBA. Do you know any VBA? Are you looking for someone to build you the VBA or are you just looking for some pointers to get you started?
     
  3. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    No, I don't know VBA and I'm not sure I could do this on my own. It would be great if someone could help me with this as it will save the daycare office staff so much time and money!! The finished XLS spreadsheet is used to do a data merge for about 10 government reimbursement forms for over 70 students.
     
  4. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    I am happy to help a bit. In the meantime, try this. I assume you have Excel 2003, Go to Tools, Macros, Record New Macro. Then do what you want the macro to do. it will record the code, which you can then look at by pressing Alt + F11. it won't be perfect code, because the recorder is quit literal, but it will give a start of how to write some code and how to be able to do this stuff in the future. Then you can compare what the recorder did, versus what I will write. You'll be on your way to VBA coding in no time!
     
  5. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    Great idea! Thanks! I will at least use it to delete the rows and columns that are consistent! I think the hard part is moving the cells, and un-merging cells that won't always be in the same spot.
     
  6. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    moving the cells is the hardest part, you'll need a loop for that, as well as a loop to delete the cells that contain "(cont)". The code to unmerge cells is easy. Just use ActiveSheet.cells.unmerge. It will unmerge all cells on the sheet, which for this purpose is good enough :)

    I am brewing something up now. Will have something for in a bit.
     
  7. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    Thank you so much for your quick work on this! I really appreciate it since it will save us so much time and money!!
     
  8. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Okay. Code written. For some reason I could not save as .xls. So I have it in .xlsm (2007 macro enabled book). See if you can save it back down to .xls. If not, let me know.

    To run code, Select Tools -> Macros -> Run Macros or Alt + T, M, M. Run the only macro that comes up against the sheet you provided in example 1 (which I loaded in there)
     

    Attached Files:

  9. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    I'm just sitting down to test it out! Thank you so much! I will post tomorrow if I need any more help. Again... thank you so much!
     
  10. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Of course, I am happy to answer any questions you have, or debug my code if something goes awry.
     
  11. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    This is working really great, such a time saver for us! I just have another question that maybe you can help me with...

    Do you know how to calculate hours from 2 times in MS Word? I have my form set up to show a row for each day of the month with:
    "Time In" | "Time Out" | "Total".

    I'd like to calculate the time elapsed and put it in the total. Not sure if this will work with Word or not, but I sure hope so! :)
     
  12. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    Ok, one more thing I hope you can do with your Macro Magic!

    Since I am using this for a time card that is merged into MS Word, I need to make sure that I have 31 Columns labeled even if there isn't any data under it.

    So in the example file that you sent, the macro will move the data for each child up onto one row and it copies the title rows (which are Date In | Time In | Rounded | Etc...) for as many days as there are

    Can you instead write it to where it just automatically copies columns B:F 30 times, THEN begin shifting all the data up?

    I'm going to play around with the code and pray I don't break it, but I imagine this might be easier for you! :)
     
  13. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    Hi again, this is working great on all of my spreadsheets except on occasion I get an error. Can you help debug this?

    It says:

    Run-time error '1004': Application-defined or object-defined error

    When I click "Debug" it highlights this row:

    .Range(.Cells(iCnt, 2), .Cells(iCnt, 6)).Cut .Cells(pasterow, 4).End(xlToRight).Offset(, 1)
     
  14. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Hi. I've been quite busy the past day or two...

    Can you attach the workbook with the sheet you are getting the error on? Otherwise, it's hard to tell what is happening. When you send the new workbook, I'll adjust the columns thing as well.

    Also, about the word question, it's best to start another thread for that for two reasons 1) It's the forums policy to keep threads specific to the original question. 2) I am pretty busy these days and wouldn't be able to immediately solve it, as Word is not my strong point.
     
  15. angelostudios

    angelostudios Thread Starter

    Joined:
    Aug 25, 2011
    Messages:
    18
    I really appreciate your help on this! I added the macro to the XLS, and you'll see the error there. This is an actual file, I only changed the names. Like I said, it is working on some of the others... do you want to see those too?
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1021890

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice