Macro editing question

[JohnD1]

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?

[RetiredGeek]

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.

[JohnD1]

Here is the test workbook. And thanks again for offering to help.

[sdckapr]

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

[JohnD1]

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.

[sdckapr]

[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

[sdckapr]

Does this code do what you want?

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

[simmo7]

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