Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Nothing and Null
What should be used with range variables if they are empty - IsEmpty(r) or r
is Nothing? Provided that r is declared as Range? What is the best way to check if range contains no cells, for example list on worksheet contains only coulumns headers? Currently I use both techniques - either Set r = Nothing and later test if r Is Nothing then...or r = Null and later test if IsEmpty(r) then... Thanks Mike510 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Nothing and Null
If r is a multicell range, then you won't want to use isempty(). That refers to
a single cell. dim r as range set r = activesheet.range("a1:b3") if application.counta(r) = 0 then 'all empty else 'not all empty end if And checking to see if a range is nothing usually means that it hasn't been assigned a range yet or you've tried to assign it to something that doesn't exist. Dim r1 As Range Dim r2 As Range Set r1 = ActiveSheet.Range("a1:b3") On Error Resume Next Set r2 = r1.Cells.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If r2 Is Nothing Then MsgBox "no formulas in " & r1.Address Else MsgBox "formulas found in: " & r2.Address End If Mikhail wrote: What should be used with range variables if they are empty - IsEmpty(r) or r is Nothing? Provided that r is declared as Range? What is the best way to check if range contains no cells, for example list on worksheet contains only coulumns headers? Currently I use both techniques - either Set r = Nothing and later test if r Is Nothing then...or r = Null and later test if IsEmpty(r) then... Thanks Mike510 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Failed to save table attributes of (null) into (null). | Excel Discussion (Misc queries) | |||
COUNTIF says Null = Blank but Blank < Null | Excel Worksheet Functions | |||
if a1 = null then a1 = b1...how do I do this? | Excel Discussion (Misc queries) | |||
cell value based on null/not null in another cell | Excel Worksheet Functions | |||
Using Nothing and Null | Excel Programming |