Here is one way to fill the blank rows simply by double clicking any cell. This assumes that the only columns you wish to fill with the data above are blank columns in columns A:D and K:L.
Copy the following event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim rng As Range, i, LastRow
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow - 1
If Cells(i, "A").Value <> "" And Cells(i + 1, "A") = "" Then
ActiveSheet.Range("A" & i & ":" & "D" & i).Copy Destination:= _
ActiveSheet.Range("A" & i + 1 & ":" & "D" & i + 1)
ActiveSheet.Range("K" & i & ":" & "L" & i).Copy Destination:= _
ActiveSheet.Range("K" & i + 1 & ":" & "L" & i + 1)
End If
Next
Target.Offset(0, 1).Select
End Sub
Select the worksheet containing your data and RIGHT click the sheet tab at the bottom.
Select 'View Code' at the bottom of the options offered.
Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').
Close the VBE (red button w/white 'x' - top right)
Double click any cell to fill the blank rows as specified in your question.
--------------------
Supplemental Shortcuts - From selected cell to end of data in active column or row:
B9 to end of data in column B = Select B9 and press: CTRL+SHIFT+ Down Arrow Key
B9 to Y9 = Select B9 to Y9 and press: CTRL+SHIFT+ Down Arrow Key
Not aware of a single keyboard shortcut to autofill a formula down through a populated range of cells in a different column.
However, you can use a macro with your own keyboard shortcut. Here is a macro that will allow you to set a formula in the first cell in the column you wish to autofill the formula, and fill the formula down through the last populated cell in the column you will select when the macro is called.
Copy this macro to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):
Sub Spl_Autofill()
Dim i, LastRow, selCol
selCol = InputBox("Enter the column letter that the formula applies to.", _
"Column Letter")
If IsNumeric(selCol) Then
MsgBox "The entry must be alphanumeric, not numeric.", vbExclamation, _
"Invalid Entry"
Exit Sub
End If
LastRow = ActiveSheet.Range(selCol & Rows.Count).End(xlUp).Row
actCol = Chr(ActiveCell.Column + 64)
Range(ActiveCell.Address).Select
Selection.AutoFill Destination:=Range(ActiveCell.Address & ":" & _
actCol & LastRow), Type:=xlFillValues
Range(ActiveCell.Address & ":" & actCol & LastRow).Select
Range(ActiveCell.Address).Select
End Sub
Press ALT + F11 (Function Key F11)
In the menus at the top of the VBE, select INSERT MODULE
Paste the macro into the white editing area to the right (right click in the area and 'Paste').
Close the VBE (red button w/white 'x' - top right)
Press ALT + F8 (Function Key F8)
When the Macros window opens, select the macro and click 'Options...' at the bottom.
Enter a letter to be used as a keyboard shortcut for this macro. To select an upper case letter, press the Shift key while selecting your letter.
Click 'OK'
To autofill a formula, enter the formula in the column and first row that you want the formula to appear.
Select any other cell, then select the 'formula' cell again.
Press CTRL + your shortcut letter (CTRL + SHIFT + your shortcut letter, if a capital letter). Enter the column letter of any column referred to in the original formula. Click 'OK'.