Hi,

Excel has an inbuilt Specialcells method that finds Blank cells. Would there be a quick (quick as in Specialcells speed) way to assign a Non-Blank range without having to loop through every cell within the Usedrange and do the IsEmpty test?

Thanks in advance,

SuperJas.

Superjas

The following will select any formulas and constants.
Union(Cells.SpecialCells(xlCellTypeFormulas),
Cells.SpecialCells(xlCellTypeConstants)).Select

Tony
-----Original Message-----
Hi,

Excel has an inbuilt Specialcells method that finds Blank
cells. Would there be a quick (quick as in Specialcells
speed) way to assign a Non-Blank range without having to
loop through every cell within the Usedrange and do the
IsEmpty test?

Thanks in advance,

SuperJas.

.

Just note that it will cause an error if you have all constants or all
formulas in your non-blank cells.

--
Regards,
Tom Ogilvy


"acw" wrote in message
...
Superjas

The following will select any formulas and constants.
Union(Cells.SpecialCells(xlCellTypeFormulas),
Cells.SpecialCells(xlCellTypeConstants)).Select

Tony
-----Original Message-----
Hi,

Excel has an inbuilt Specialcells method that finds Blank
cells. Would there be a quick (quick as in Specialcells
speed) way to assign a Non-Blank range without having to
loop through every cell within the Usedrange and do the
IsEmpty test?

Thanks in advance,

SuperJas.

.

This is how i approached a similar problem

Sub CopyDown(ByVal intCol As Integer, ByVal intStartRow As Integer)
'Macro for Copying Information down to blank cells
'From Data from Pivot Tables
'Written by DC on 15/01/2004

Dim dblLastRow As Double, dblRow1 As Double, dblrow2 As Double
Dim intMyRow As Integer
Dim strMyRange As String, StrMyText As String

dblLastRow = Range("A" & Rows.Count).End(xlUp).Row
dblrow2 = intStartRow

Do Until Cells(dblrow2, intCol).Row dblLastRow
dblRow1 = dblrow2
StrMyText = Cells(dblRow1, intCol).Formula

dblrow2 = Cells(dblRow1, intCol).End(xlDown).Offset(-1, 0).Row
If Cells(dblrow2, intCol).Formula = "" And dblrow2 <
dblLastRow Then
Range(Cells(dblRow1, intCol), Cells(dblrow2
intCol)).Formula = StrMyText
dblrow2 = dblrow2 + 1
ElseIf dblrow2 dblLastRow Then
dblrow2 = dblLastRow - 1
If Cells(dblrow2, intCol).Formula = "" Then
Range(Cells(dblRow1, intCol), Cells(dblrow2
intCol)).Formula = StrMyText
dblrow2 = dblrow2 - 1
End If
Exit Do
Else
dblrow2 = dblrow2 + 1
End If
Loop
End Sub

I passed two arguments in the inital instance one for the start row an
one for the start column

Hope this Helps

Davi

--
Message posted from http://www.ExcelForum.com