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: Simple Excel Macro Required

Discussion in 'Business Applications' started by Mike McBain, Sep 20, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    398
    G'day Magicians

    I am an incompetent Excel macro creator and would appreciate some help.

    I have a spreadsheet column with text in it
    I want to move every second row two cells right and one row up
    I want to delete every third row

    Should be easy but I've just wasted two hours trying to do it can someone please help me tonight?

    Forever grateful

    Mike.
     
  2. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Hi Mike

    For the first part this should. It assumes that your data is in one column, column A and starts at cell A1. You can always adjust the code to suit.

    Code:
    Sub MoveRows()
    Dim x As Long
    
    For x = 2 To Range("A65536").End(xlUp).Row Step 2
    Range("A" & x).Cut Destination:=Range("A" & x).Offset(-1, 2)
    Next x
    End Sub
    
    As for deleting rows, if you delete every third row, you will be deleting data - is this what you want?

    Regards
     
  3. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    398
    Glaswegian

    Many thanks and yes every third row is a repeat of useless info.
    The problem I always seem to encounter when recording macros is that the record puts indidual cell refs in when all I want it do do is show Up Down Left or Right moves. Is there a way around that?

    Mike - Taswegian
     
  4. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Mike

    Can I just check - now that we've moved the data, there are now blank rows every second row. So deleting every third row...? Do you mean delete every third row that contains data?

    Just wanted to check first.

    Taswegian - I like it! :D
     
  5. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Mike

    The recorder records everything as done in the Excel interface. Because it can't work out what you might actually want, it just records everything. The recorder is useful for providing basic code and objects and properties - a great deal of what it produces can be discarded or amended. And you'll notice that the recoder cannot record a variable - using variables can make your code faster, easier to understand and quicker to write (to name but a few). In the first bit of code I posted, I assigned the variable 'x' to the last row containing data. So rather than constantly write out Range("A65536")..blah blah, I can just use 'x' - and then re-use it elsewhere in the same code.

    OK, I'll stop rabbiting on now...
     
  6. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    398
    Thanks Glaswegian, I was spoilt by an early version of Lotus 123 which actually recorded keystrokes and I thought was magnificient but even they discontinued it and it wouldn't convert to Excel anyway.

    Do you have any web refs handy that might help a beginner like me to learn how to create Macros - I have a number of more complicated reqts coming up?

    With thanks

    The Taswegian
     
  7. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Yes, Lotus solved many of the macro recorder problems over 20 years ago - and Microsoft still can't quite get it right. Did you sort the delete rows piece?

    For Excel specific websites try

    MrExcel - probably the biggest and best for Excel.
    Ozgrid for Excel & VBA help.
    VBA Express for Excel and MS Office help.
    John Walkenbach and Chip Pearson for lots of Excel code, hints and tips.

    Regards
     
  8. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    398
    Yes fixed the row delete OK
    Thanks for the references I will check them all out late tonight.

    With thanks

    Taswegian
     
  9. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Good luck (y)

    You might find me on some of the Excel forums as well :D
     
  10. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    398
    G'day Glaswegian

    I have not yet been able to find a relevant lesson for my next problem and would again appreciate your help.

    The attached xls spreadsheet shows how my horse racing data for tomorrow comes in columns A to D

    I need to rearrange the Race data in these first three columns as it is shown in the sample columns G to J

    The number of races can vary from 6 -10 and the number of horses in each race can vary from 4 - 24

    The extra line of text "APPRENTICES CANNOT CLAIM" can appear under any or none of the Race numbers

    I have a number of these pages to convert every day and doing it by manually cutting and pasting is driving me up the wall!

    Trying to record a macro is useless because of the variables and trying to find the instructions to do something like this is even more difficult.

    With thanks in anticipation

    Mike from Taswegia
     

    Attached Files:

  11. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Hi Mike

    You Project was locked so I've just copied your sheet into a new book.

    I've done this fairly quick so it might not be the best option but here goes. I've used an Input Box to get the range you want to move/copy - just click and drag to highlight the relevant cells. Then another input box to find out where you want to put the data - just select the first cell. The data is then copied. You might want to create a fresh sheet for your new data. The Input Box uses validation by means of defining the Type (in this case 8) to ensure that a range of cells is selected. Use the help files to read up some more on this. Note that there is some limited error handling.

    If this is not working for you or I've misunderstood anything just post back.

    Regards
     

    Attached Files:

  12. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    398
    Glaswegian

    Many thanks for your very prompt response!
    I'm sorry to tell you that I really am a beginner at this macro thingo
    I don't even know what you mean by my sheet being "locked" and have no idea what an "input box" is?
    When I open your sheet it looks just like mine but where and how do I find any macro or instructions?
    BTW I am using Excel 2002 (10.6501.6626) SP3
    Sorry to be a nuisance

    with thanks

    Mike
     
  13. Glaswegian

    Glaswegian Malware Specialist

    Joined:
    Dec 5, 2004
    Messages:
    3,823
    Sorry Mike

    My apologies for making assumptions.

    Open the workbook I attached - it's just a copy of yours. Now press Alt+F8 (hold down the Alt key and press the F8 key). The macro dialogue box will appear. Look for a macro called 'MoveTheData' - click to highlight it and then click the Run button. Just follow the prompts as I described earlier.

    Regards
     
  14. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    398
    Glaswegian

    I have found a macro called "Move TheData" under Tools Macro Macros which is your creation I guess but if I have to select the cells it is much slower than cutting and pasting?

    It also seems to leave the old data in place and I have to select and delete that?

    Maybe its all too difficult for an old incompetent like me?

    With thanks

    Mike
     
  15. Mike McBain

    Mike McBain Thread Starter

    Joined:
    Oct 27, 2001
    Messages:
    398
    Glaswegian

    Our posts crossed.
    I have found the Alt F8 and opened the macro box, (I have lots of old attempts in there) which need deleting.

    Unfortunately it seems to be copying rather than moving the data and as mentioned is a much procedure than cutting and pasting so I guess for now I should go to bed and sleep on it all before my big day tomorrow...we have our Aussie Rules Grand Final and a big race day with lots of meetings

    Thanks again and goodnight from

    a sleepy Taswegian in Tasmania
     
  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/400572

  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