Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Macro editing question

    I want to do a little macro to save some time with a nuisance spreadsheet. I have data that is irregularly spaced in rows. I want to
    • start at the lowest empty cell
    • effectively hold down the shift key while I use the END-UP ARROW command
    • Continue holding the shift key, use the RIGHT ARROW command to highlight the area
    • Copy Down
    • Use the END-UP ARROW command
    • plus one more UP ARROW to move to the next empty cell
    • Rinse, Lather, Repeat
    I recorded the macro:

    Sub Copying_Stuff()
    '
    ' Copying_Stuff Macro
    '
    '
    Range(Selection, Selection.End(xlUp)).Select
    Range("F7984:Q7989").Select
    Range("F7989").Activate
    Selection.FillDown
    Selection.End(xlUp).Select
    Range("F7983").Select

    As you see, it has specific cell mentions. How do I revise it to give me a variable range?

    Note that the column range is large. I hide the intermediate columns, and use a simple "right" arrow to highlight the area of interest. (There are occasional blank cells; this seems to prevent these from confusing the system.)

    While I would like to completely automate it, this would require some sort of stop command (say when it hits a cell with some sort of keyword?). But that probably adds too much complexity. Just hitting a simple keyboard shortcut repeatedly (A hundred times) is still better than getting my fingers repeatedly twisted and hitting the wrong keys.

    Speaking of shortcuts: I would like to assign it to a vacant keyboard shortcut. Is there anything like in Word that will show me the currently used keyboard shortcuts? And, am I forced to use the Ctrl only?
    [I have been here for years; I had to get things restarted]

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    It's a little hard to tell what you want from your description, at least for me.
    Would it be possible for you to create & post a test workbook with the data to copy in one color and the copied data in another color? This would let us see what actually gets done. What you are asking is very doable if we know exactly what has to be selected and copied.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Here is the test workbook. And thanks again for offering to help.
    Attached Files Attached Files
    [I have been here for years; I had to get things restarted]

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can do this easily without a macro.
    Unhide the columns
    Select A1. Hold the shift and <end><down>
    With shift still being held, move over to cover all the columns desired (F in this example).
    Home - Find and select- Edit - goto special
    Check "blanks" <ok>
    [Now all the blanks are selected]
    in the cell, enter an equal and goto the cell above
    confirm with <ctrl><enter> and the formula will be placed in all the blank cells

    Select all the data, copy and paste-special values to convert the formulas.

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    JohnD1 (2011-10-27)

  6. #5
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    HOLY MACKERAL THERE BATMAN!!!

    WOW!!!

    I have never seen this "Find and Select" before. And I thought I was a reasonably good Excel user in spite of my lack of macro programming. And, the <ctrl><enter> command is something I had not seen before. [PS--I like your use of the old WordPerfect description of keystrokes]

    EDIT: I just tried it. There is one problem. In Reality, many of my cells have formulas. This technique does not carry down the formula. But for other parts of the worksheet, this is a miracle worker.
    Last edited by JohnD1; 2011-10-27 at 12:39.
    [I have been here for years; I had to get things restarted]

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    [PS--I like your use of the old WordPerfect description of keystrokes]
    I never used WordPerfect. This was for me the description of the Lotus123 keystrokes (and the basis for its "mnemonics" for keystrokes and its macros...) and I carried over as a simple way to describe them in XL.

    Steve
    PS this option before XL2007 was the Edit - goto special... dialog box
    Last edited by sdckapr; 2011-10-27 at 14:40.

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does this code do what you want?

    Code:
    Option Explicit
    Sub FillBlanksFromAbove()
      Dim rng As Range
      Dim rBlanks As Range
      Dim rArea As Range
      Dim rCell As Range
      
      Set rng = Range("A7").CurrentRegion
      rng.EntireColumn.Hidden = False
      Set rBlanks = rng.SpecialCells(xlCellTypeBlanks)
      For Each rArea In rBlanks
        For Each rCell In rArea
          rCell.FormulaR1C1 = rCell.Offset(-1, 0).FormulaR1C1
        Next rCell
      Next rArea
      Set rCell = Nothing
      Set rArea = Nothing
      Set rBlanks = Nothing
      Set rng = Nothing
    End Sub
    It uses some of the same techniques I did manually, only instead of just reading the cell above, it "copies" the formula from teh cell above.

    Steve

  9. #8
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by sdckapr View Post
    You can do this easily without a macro.
    Home - Find and select- Edit - goto special
    Steve
    Steve, the F5 key is a quicker way to the goto special command - I use it all the time

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

Posting Permissions

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