• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fill in Blank Cell from Cell Above

DSAshley

New Member
I know this sounds like a no-brainer; yes I can always Ctrl " or drag the information down or even copy and paste. My issue is I have over 75,000 lines of data and little time to accomplish this task. Not all cells are blank and it is very sporadic.


My question - Is there a formula I could use to "make this happen" in little time? If so please explain how to use this - I've got very little experience in formulas - so please be patient with me. :)


Thanks for your advice in advance.
 
I assumed that all your data is in column A and then type this in column B, B2: =IF(A2="",A1,A2). Drag the formula till the end of your data and then copy column B and Paste Special as value in column A.


Let me know if anything else is needed!
 
1. Highlight entire range of cells containing blanks and non-blanks

2. Press Ctrl+G (the goto dialogue)

3. Press Special, select blank cells (note, they must truly be blank, not formulas that evaluate to "")

4. Type "=" (sans quotes) and then hit the up arrow.

5. Press Cltr+Enter to input that formula into all currently selected blank cells.

DONE
 
PSG - You are my HERO for not only the day, week, month but for the year!!!! ....and we are not even half way through the first month. With tweaking the forumla to fit my cells and columns this worked like a charm. Since my blanks were sporadic I filtered that column and pasted the formula into the first blank cell and ran it down. I didn't know if this would copy the one above since the filter was on but it did correctly. Now I wish I asked the question last week.


Luke M - Sorry but I have not had a chance to try your instructions.


Thanks to both of you for replying!!! I believe in working smarter not harder and you made that happen.
 
Luke M - Thanks for the solution. I generally use the same formula which PSG gave. but for the cases where more than one blank cell is there below. I end up running the formula again n Again. Ur solution works for any number of blank cells below.
 
I use this when I break a pivot table from an OLAP source and want to create a new pivot table. This code takes whatever your selection is and fills down until it hits a cell with values. Works great when you have multiple sets of blanks that you want filled from above.


Sub FillBlankLines()


ScreenUpdating = False


Dim rngSelection As Range

Dim rngCell As Range


Set rngSelection = Selection


For Each rngCell In rngSelection

If rngCell.Value = "" And rngCell.Row <> 1 Then_

rngCell.Value = rngCell.Offset(-1, 0).Value

Next


ScreenUpdating = True


End Sub


Jesse
 
Back
Top