Excel Autofill to a certain column?

Question

I work a lot with very large worksheets. Some have hundreds of thousands of rows.

It happens quite that I have a column that I have to autofill a column from row 2 through row 279,495 (for example) and the columns around it have lots of empty cells so double-clicking the corner of the cell won't do it. So, I have been clicking and dragging and dragging and dragging and dragging... And it takes a really not time. 

Do you know any tricks to do this faster?

garbo7441 · Accepted Answer

Here is one way using an event handler.  You don't state your column letter, so this example uses column F.  If your column is not 'F', change the "F" references to your column letter, i.e. "M", "K", etc.

Then, copy the event handler below, modified if needed, 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)
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F279495"), Type:=xlFillDefault
End Sub

Select the worksheet containing your data and right click the sheet tab at the bottom.

Select 'View Code'.

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.

saki · Answer

You can simply use the function field at the top and type out your selection. $a2:$a279495. This will automatically select the rows you want.

Trending News

Excel Autofill to a certain column?

2 Answers