Selecting Blank Cells with in the selection

[vishwakarma]

Hi,

I have a large amount of data in excel sheet (about 130000+) in 4 columns. I tried to select the blanks cells with in the data through "Go To Special" dialog box, but it is showing me an error saying "Selection is too large". Can any one help me on this...


Thanks in advance...

[Simon Lloyd]

Why do you want to select blanks?, you could just use Autofilter and filter for blanks.

[vishwakarma]

actually I want to the fill empty cells with the value of the upper cell.


Thanks,

[Bob Phillips]

If I create a 13000 cell area over 4 columns that are mainly blanks, I can select the fine. I guess you must have too many separate blank areas in that range.

Can you select them in groups, say the first 1,000 lines, and so on.

[vishwakarma]

Thanks for the solution, XLD.. it works and yes i've separate blank areas in the range.

But this solution will still take time as I have to go by 1000 or may be 2000 records at a time, Is there any way by which we can select these blanks cells at a time...


Thanks,

[Bob Phillips]

I agree it will be time consuming, but I don't think ther is any way to access all the areas, there is a limit and I am not aware of how to circumvent that. You could create a macro to do it for you.

[vishwakarma]

Here what I got...

[VBA]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

[/VBA]