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

--
kralj
-----------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...nfo&userid=995
View this thread: http://www.excelforum.com/showthread.php?threadid=39516

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

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(xlCellTypeFormul as), _
myRange.SpecialCells(xlCellTypeConstants))
End Functio

--
kralj
-----------------------------------------------------------------------
kraljb's Profile: http://www.excelforum.com/member.php...nfo&userid=995
View this thread: http://www.excelforum.com/showthread.php?threadid=39516