Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for empty range in vba | Excel Discussion (Misc queries) | |||
check a range of cells to see if they are empty | Excel Programming | |||
Clear cells range if certain cells are all empty | Excel Programming | |||
sum next two non-empty cells in a range | Excel Worksheet Functions | |||
Check for empty cells in Print Area? | Excel Programming |