Does Range("A1,B2,C3,etc") have a max number of cells?

[kraljb]

I am having an issue with a macro that I wrote, it takes a range 236rows by 100 columns and then creates a new range that is only cells with values in them, or rather it removes all the blanks (Which their tend to be quite a bit). Excel crashes consistently when I am running this. If there is a max, what is it? Maybe there is a better way for me to approach this problem.

Thank you in advance for your help,

The code for removing the blanks is as follows... The CombineRange function acts much like Union, but It checks to make sure that RemoveBlanks and CurrCell are in fact already ranges.

Function RemoveBlanks(myRange As Range) As Range
Dim CurrCell As Range
For Each CurrCell In myRange
If Not Len(CurrCell.Formula) = 0 Then
Set RemoveBlanks = CombineRange(RemoveBlanks, CurrCell)
End If
Next
ErrHandler:
With Err
If Not .Number = 0 Then
.Raise .Number, "mdlFormat:RemoveBlanks" & vbCrLf & .Source, .Description
End If
End With
End Function

[Jim Thomlinson]

I think this is what you are looking for...

Sub test()
Call RemoveBlanks(Sheet1.UsedRange)
End Sub

Public Sub RemoveBlanks(ByVal rng As Range)
rng.SpecialCells(xlBlanks).Delete
End Sub
--
HTH...

Jim Thomlinson


"kraljb" wrote:

>
> I am having an issue with a macro that I wrote, it takes a range 236rows
> by 100 columns and then creates a new range that is only cells with
> values in them, or rather it removes all the blanks (Which their tend
> to be quite a bit). Excel crashes consistently when I am running this.
> If there is a max, what is it? Maybe there is a better way for me to
> approach this problem.
>
> Thank you in advance for your help,
>
> The code for removing the blanks is as follows... The CombineRange
> function acts much like Union, but It checks to make sure that
> RemoveBlanks and CurrCell are in fact already ranges.
>
> Function RemoveBlanks(myRange As Range) As Range
> Dim CurrCell As Range
> For Each CurrCell In myRange
> If Not Len(CurrCell.Formula) = 0 Then
> Set RemoveBlanks = CombineRange(RemoveBlanks, CurrCell)
> End If
> Next
> ErrHandler:
> With Err
> If Not .Number = 0 Then
> .Raise .Number, "mdlFormat:RemoveBlanks" & vbCrLf & .Source,
> .Description
> End If
> End With
> End Function
>
>
> --
> kraljb
> ------------------------------------------------------------------------
> kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955
> View this thread: http://www.excelforum.com/showthread...hreadid=395160
>
>

[kraljb]

Thanks for the help Jim, but that was not what I was looking for, although it did help me get what I needed...

Function RemoveBlanks2(myRange As Range) As Range
Set RemoveBlanks2 = CombineRange(myRange.SpecialCells(xlCellTypeFormulas), _
myRange.SpecialCells(xlCellTypeConstants))
End Function