check for non-empty cells outside of a certain range
 

If I have a range of cells, say Range(Cells(1,1), Cells(50,50)), what
is the easiest way to check that all the other cells on that sheet have
no formulas or constants in them?

Thanks in advance,

Abe

check for non-empty cells outside of a certain range
 

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column
if RealLastRow 50 or RealLastColumn 50 then
msgbox "entries outside range"
else
msgbox "no entries outside range"
end if
End Sub

--
Regards,
Tom Ogilvy


"Abe" wrote:

If I have a range of cells, say Range(Cells(1,1), Cells(50,50)), what
is the easiest way to check that all the other cells on that sheet have
no formulas or constants in them?

Thanks in advance,

Abe

check for non-empty cells outside of a certain range
 

Tom,
Is that because you cannot trust the result of UsedRange ?

NickHK

"Tom Ogilvy" ...
Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column
if RealLastRow 50 or RealLastColumn 50 then
msgbox "entries outside range"
else
msgbox "no entries outside range"
end if
End Sub

--
Regards,
Tom Ogilvy


"Abe" wrote:

If I have a range of cells, say Range(Cells(1,1), Cells(50,50)), what
is the easiest way to check that all the other cells on that sheet have
no formulas or constants in them?

Thanks in advance,

Abe

check for non-empty cells outside of a certain range
 

I trust it (usedrange) implicitly. It tells me what cells Excel is actually
maintaining data about. It tells me nothing about whether these cells can be
considered empty or not. So if I am trying to misuse it (drive a screw in
with a hammer) then the answer to your question would be yes. <g If I
recognized what it actually is for, then I am using my posted approach
because there is no built in command that will tell me what the extent of non
empty cells is (except special cells, but that would be more cumbersome).

--
Regards,
Tom Ogilvy






"NickHK" wrote:

Tom,
Is that because you cannot trust the result of UsedRange ?

NickHK

"Tom Ogilvy" ...
Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
On Error Resume Next
RealLastRow = _
Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column
if RealLastRow 50 or RealLastColumn 50 then
msgbox "entries outside range"
else
msgbox "no entries outside range"
end if
End Sub

--
Regards,
Tom Ogilvy


"Abe" wrote:

If I have a range of cells, say Range(Cells(1,1), Cells(50,50)), what
is the easiest way to check that all the other cells on that sheet have
no formulas or constants in them?

Thanks in advance,

Abe