Friday, March 27, 2009

Macros in Excel : Fill Blank Cells With Data Above

In case you need to fill the blank cells within a range with the data above it , use following macro code. Since this works on pre selected range of data, so make sure that you have selected the required data range before running your macro.

Let us take an example. if you want to fill the blank cells with in range "A2:D10", make sure that you have select this range before running the macro.

Sub Fill_Blank_Cells()
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"

End Sub

Please also refer to earlier post on "How to fill blank cells in excel data range with cells above it"
Posted by Yogesh Gupta at 6:47 PM
Labels: ,

4 comments:

  1. The PowAugust 22, 2009 at 4:55 AM

    really healpful!!!!

    thanks

    ReplyDelete
  2. UnknownJune 15, 2010 at 8:07 AM

    How about fill blank cells in excel data range without specific range. for example A2:#

    ReplyDelete
  3. Yogesh GuptaJune 15, 2010 at 2:21 PM

    In all the cases you will need to know the range you need to fill. There may be cases where the range is dynamic and will keep changing. For such cases you can use range name in your macro instead of fixed reference.

    ReplyDelete
  4. Mallappa HanabarApril 1, 2013 at 10:10 PM

    hi Yogesh.. The macro is not functioning properly if I select more cells i.e. A1 - H100000 or so.. Please give me a solution

    ReplyDelete

Subscribe to: Post Comments (Atom)