Replacing blank cells in Excel 3

[qster]

Hello,

I wanted to ask if any one here know of a formula or step to replace any blank cells in Excel with the value located above the blank cell.

Here is an example:
Prov Org Owner Name Value
SK 12345 Scott John,Smith 4
2
5
SK 12344 Scott Jane,Doe 2
6
SK 12342 Scott Charlie,Brown 7
5
SK 14532 Scott Rieger,Michael 6
ON 11111 Scott Simpson,Stephen 9
ON 10010 Scott Chiu,Tak 3

What I am trying to do is replace the blank cells (over 1000 of them) with the value that they should be tied to above them. So where it says "Prov" all cells in that column should have SK or ON and not a blank. Same should apply for the rest of the columns.

Q

 

[xlbo]

Run this on each column:
Sub Fill_Pivot_Data()
Dim lRow As Long
Const sRow = 2
Const uCol = 1
lRow = Cells(65536, uCol).End(xlUp).Row
For i = sRow To lRow
If Cells(i, uCol).Text = "" Then Cells(i, uCol).Value = Cells(i - 1, uCol).Value
Next i
End Sub

change sRow to be the starting row of your data and uCol to be the column NUMBER of the column you are running this on

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[KenWright]

Select the entire range of data and do Edit / Go To / Special / Blanks - You will now see that all the blanks are selected, and that the first blank is the active cell.

Just type = and then click on the cell above the activecell and hit CTRL+ENTER. This will put what you need in every cell, and now assuming that you have no formulas in that lot, select th enetire range, do Edit / Copy and then Edit / Paste Special / Values.

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[xlbo]

Dammit - I always forget that Ken. I'm sure I've given you a star in the past for this solution but here's another one anyway 'cos I love the simplicity of it

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jdhilljr]

Ken,

I know I need to give you a star for that one. Keep the good ones coming.

Jim

 

[KenWright]

LOL - It's one of my favourite features - That and Pivot Tables and the SUMPRODUCT function.

Regards
Ken..................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------