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