Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro-making relative rather than absolute (2003)

    Edited by HansV to present data in table format

    I have just copied data (thousands of contacts/records) from an internet site into an Excel spreadsheet, most of which (not all records) now looks like this in the Excel spreadsheet:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>John Doe</td><td>ABC Company</td><td>t. 212-222-2222</td><td align=center>2</td><td align=right>*</td><td align=right>*</td><td>f. 212-444-4444</td><td align=center>3</td><td align=right>*</td><td align=right>*</td><td>jdoe@abc.com</td><td align=center>4</td><td>Jim Smith</td><td>Company Z</td><td>t. 555-123-4567</td><td align=center>5</td><td align=right>*</td><td align=right>*</td><td>f. 555-123-4568</td><td align=center>6</td><td align=right>*</td><td align=right>*</td><td>jim.smith@coz.com</td></table>
    I want to cut the fax number and the e-mail address and paste them into separate fields/columns on the row w/name, but rather than do thousands by hand, I was hoping to create a macro that would mimic my keystrokes; however, the macro records absolute and doesn't work the way I want it to. Can anyone tell me how I can make the following macro relative to where my cursor is when I run it? I'd really appreciate any advice I can get. Many thanks!....mjairix

    Sub Macro1()
    Range("E80").Select
    Selection.Cut
    Range("F79").Select
    ActiveSheet.Paste
    Range("E81").Select
    Selection.Cut
    Range("G79").Select
    ActiveSheet.Paste
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-making relative rather than absolute (2003)

    Your macro doesn't seem to fit the data you posted (but it's hard to say because all the spaces disappeared - HTML ignores multiple spaces).
    With the data as in the table I created, you could use this macro:

    Sub MoveCells()
    Dim r As Long
    Dim m As Long
    ' Last row in column C
    m = Cells(Rows.Count, 3).End(xlUp).Row
    ' Start two rows up from the end
    For r = m - 2 To 1 Step -3
    ' Transfer cells to columns D and E
    Cells(r, 5) = Cells(r + 2, 3)
    Cells(r, 4) = Cells(r + 1, 3)
    ' Then delete the superfluous rows
    Range(Cells(r + 1, 1), Cells(r + 2, 1)).EntireRow.Delete
    Next r
    End Sub

    You can adapt the macro to your purposes.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro-making relative rather than absolute (2003)

    Oh, Hans, thank you, I was hoping you were online! I tried running the macro, but I get a "complie error - syntax error" on the following line:
    For r = m - 2 To 1 Step -3
    You are right, my example had spaces so it didn't appear as I wanted it to. Basically, here's my scenario. I will always have my cursor in a Column E field (telephone #) and some unknown row number. I want the macro to move down one row, select and cut the data (fax #), move over to Column F, move up one row and paste that data, then move back over to Column E (telephone #) and down two rows, select and cut that data (e-mail address) and move over to Column G and up two rows and paste. Does that make sense?

    If you can send me another script to try, I'd greatly appreciate it. Many thanks!...Mary


    Because not all of the thousands of records have a fax number and/or an e-mail address, I don't want the macro to keep going. If the macro will work once and then stop, I can keep running it again when I need it. Also, removing the rows that then become blank will be a great help, too. Thank you!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-making relative rather than absolute (2003)

    Try this:
    <code>
    Sub MoveCells()
    ' Transfer cells to columns F and G
    ActiveCell.Offset(0, 1) = ActiveCell.Offset(1, 0)
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(2, 0)
    ' Then delete the superfluous rows
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(2, 0)).EntireRow.Delete
    End Sub</code>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro-making relative rather than absolute (2003)

    Hooray! It now works just like I wanted it to. Thank you so much!

    Would it be too much to ask you for a little macro that will delete all blank rows between records after I finish cleaning up this worksheet of numerous records, because there will still be blank rows, even after the macro deletes the two rows that had the fax# and e-mail address previous to running the macro?

    Many, many thanks! You are so good at what you do!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-making relative rather than absolute (2003)

    Can the macro look at a specific column to check whether the row can be deleted, or should it check a number of columns. Which one(s)?

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro-making relative rather than absolute (2003)

    I think Column C (Last Name) would be the safest, Hans, so if there is no data in Column C, the row can be deleted.

    I guess I was thinking that a row could be read in entireity and then if all column fields were blank, the row could be deleted.

    Many thanks for helping me with this--it will be a great help!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro-making relative rather than absolute (2003)

    The following macro will delete all rows that have no data in column C:
    <code>
    Sub DeleteBlankRows()
    Range("C:C").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
    End Sub</code>

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro-making relative rather than absolute (2003)

    Many thanks, Hans. I'll run this macro after I finish all cleanup. I appreciate your help more than you know! All good wishes...Mary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •