How to find value in Excel column and then fill down to a particular row

**Aienstaien** · 01-07-2009

I need a macro that searches for a column in a data bank by heading. In my excel sheet have written a macro to format some data. However there is a section of the macro that fills a column with a formula from the 11th row to the last used row. When you use a range this way it sets the formula of ALL cells in that Range to that formula. I think it may have something to do with the .UsedRange.Rows.Count part.

**Alejandro** · 01-07-2009

Try the following code... this will search for a particular string (partial strings will also work) and then it will add 20 more rows of data to the last cell. I want to do a special fill down that goes to, but not past, the lowest filled cell on the spreadsheet. I can find the last-filled cell in a single column.

Code:

Sub Fill20Down()
    Dim rng As Range
    Set rng = ActiveCell.End(xlDown)
    Range(rng, rng.Offset(20, 0)) = rng
End Sub

**pancham** · 01-07-2009

If you column is populated, then you would hit the End Key, then the down arrow. Select the cells in the column, starting in the row below the column heading. Choose Edit | Go To special button, Type an equal sign, Press the up arrow on the keyboard -- this will enter a reference to the cell above, If you want to select from the current cell down to the bottom, then hold down the shift key while you hit End, then the down arrow.

**PARRISH** · 01-07-2009

You may also use the following code in your program and let me know what output you get, this code i found on the internet.

Code:

Sub Fill_Empty()
 '--David McRitchie,  2003-07-24,  fillempt.htm 
 '--Macro version of -- Excel -- Data Entry -- Fill Blank Cells
 'http://www.contextures.com/xlDataEntry02.html
 'http://www.pcworld.com/shared/printable_articles/0,1440,9346,00.html
    Dim oRng As Range
    Set oRng = Selection
    Selection.Font.Bold = True
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Font.Bold = False
    Selection.FormulaR1C1 = "=R[-1]C"
    oRng.Copy
    oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
End Sub