Finding Non-Blank Cells

S

SuperJas

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.
 
A

acw

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
Click to expand...
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?
 
T

Tom Ogilvy

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

DSC

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
 
Top