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 236row
by 100 columns and then creates a new range that is only cells wit
values in them, or rather it removes all the blanks (Which their ten
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 t
approach this problem.

Thank you in advance for your help,

The code for removing the blanks is as follows... The CombineRang
function acts much like Union, but It checks to make sure tha
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 Functio

 

[Guest]

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

 

[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 Functio