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
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
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
>
>
Tom,
Is that because you cannot trust the result of UsedRange ?
NickHK
"Tom Ogilvy" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
> 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
>>
>>
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" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
> > 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
> >>
> >>
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks