.SpecialCells(xlCellTypeBlanks) does not work 2

[Gavona]

Selection too large" is the error message if you do it manually. In code there is no warning the next action merely applies to all cells rather than just the blank ones.

http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

However, when you use a VBA macro to make the same or a similar selection, no error message is raised and no error code is generated that can be captured through an error handler....
To work around this behavior, you may want to create a looping structure in your VBA macro that handles less than the maximum 8,192 cells.....

OK so fair enough but to say:

This behavior is by design.

seems a bit rich - no error your code just messes up your data!!!

Ok rant over, now how do I best fix my code? I regularly use it for large blocks of cells (lots of rows x several columns). As the code is often built into a larger routine I simply would not spot the issue so I want to avoid it.

Sub FillBlanksFromAbove()

    Dim r As Range, r2 As Range
    Dim i As Integer
    ' Get around Excel limitation:  [URL unfurl="true"]http://support.microsoft.com/default.aspx?scid=kb;en-us;832293[/URL]
    Set r = Selection
    
    [red]For i = 1 To r.Rows.Count Step Round((8192 / r.Columns.Count), 0)[/red]
        Set r2 = Range(r.Cells(i, 1), r.Cells(Application.WorksheetFunction.Min(r.Rows.Count, 8192 / r.Columns.Count)))
        r2.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        r2.Value = r.Value
    Next i

End Sub

This debugs at the red bit with the error "Run time error '6' overflow

I added the Round bit to see if that was the issue - no joy
In my test
r.rows.count=47,620
r.columns.count=1

It's late so I will look again tomorrow afternoon/evening. Thanks for any pointers.

Gavin

 

[xlbo]

...wonders what the largest integer value is in VBA....and subsequently why he always uses a long variable type for row incrementing.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

In other words...

    Dim i As Integer 'max postive INTEGER value 32,767

RATHER

    Dim i as LONG

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

aw

http://www Skip

- you spoiled the puzzle for Gavin ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Got too much time on my hands.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Plenty more puzzles here! Just wish my favourite Excel consultant hadn't gone and got himself so much other work.

Here is my final code - there were other issues with the original (it was 11pm!)

Sub FillBlanksFromAbove()
    Dim r As Range, r2 As Range
    Dim i As Long
    Dim myCalc
    myCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    ' Get around Excel limitation:  [URL unfurl="true"]http://support.microsoft.com/default.aspx?scid=kb;en-us;832293[/URL]
    Set r = Selection
    For i = 1 To r.Rows.Count Step Round((8192 / r.Columns.Count), 0)
        Set r2 = Range(r.Cells(i, 1), r.Cells(Application.WorksheetFunction.Min(r.Rows.Count, i + 8192), r.Columns.Count))
        r2.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        [red]r2.Calculate 'not sure this is necessary[/red]
        r2.Value = r.Value
    Next i
Application.Calculation = myCalc
Application.ScreenUpdating = True
End Sub

Thanks for the hint Geoff - it was enough though had I read it last night then Skip's would have been hugely appreciated as my brain was clearly not in top gear!

Gavin