Find the last row and column of the dirty area
 

Hi,
My problem is that if you enter a value in a cell in say row 65,000 and then
delete it excel will still think the sheet goes to row 65,000. I need to know
what row and column Excel thinks the sheet goes to.
I've found lots of ways to get it to find the real answer but nothing on how
to find out what excel thinks it is.

Thanks MarkS

Find the last row and column of the dirty area
 

Try this

'Last row filled in Column A
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Last col filled in Row1
lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column


If this post helps click Yes
---------------
Jacob Skaria


"MarkS" wrote:

Hi,
My problem is that if you enter a value in a cell in say row 65,000 and then
delete it excel will still think the sheet goes to row 65,000. I need to know
what row and column Excel thinks the sheet goes to.
I've found lots of ways to get it to find the real answer but nothing on how
to find out what excel thinks it is.

Thanks MarkS

Find the last row and column of the dirty area
 

Sub ThisIsIt()
Dim rngCorner As Range
Set rngCorner = Cells.SpecialCells(xlCellTypeLastCell)
MsgBox rngCorner.Address
End Sub
--
Jim Cone
Portland, Oregon USA



"MarkS"

wrote in message
Hi,
My problem is that if you enter a value in a cell in say row 65,000 and then
delete it excel will still think the sheet goes to row 65,000. I need to know
what row and column Excel thinks the sheet goes to.
I've found lots of ways to get it to find the real answer but nothing on how
to find out what excel thinks it is.
Thanks MarkS

Find the last row and column of the dirty area
 

hi
how much space on a sheet that excel "thinks" has been used is called
UsedRange.
Excel records how much space on a sheet has be used and uses this to do a
number of thinks such set the scroll bars, top and side. some times excel
hicups and looses it reference and UsedRange needs to be reset. a relitive
common post. see this sit on how to reset UsedRange if ever needed....
http://www.contextures.com/xlfaqApp.html#Unused

but to see how much space on a sheet that excel "thinks" has be "used", run
the following macro..
sub howmuchspace()
activesheet.UsedRange.select
end sub

this will highlight the space that excel "thinks" has been used.

regards
FSt1

"MarkS" wrote:

Hi,
My problem is that if you enter a value in a cell in say row 65,000 and then
delete it excel will still think the sheet goes to row 65,000. I need to know
what row and column Excel thinks the sheet goes to.
I've found lots of ways to get it to find the real answer but nothing on how
to find out what excel thinks it is.

Thanks MarkS

Find the last row and column of the dirty area
 

Or, if the OP needs this information inside his code...

HowMuchSpace = ActiveSheet.UsedRange.Address

--
Rick (MVP - Excel)


"FSt1" wrote in message
...
hi
how much space on a sheet that excel "thinks" has been used is called
UsedRange.
Excel records how much space on a sheet has be used and uses this to do a
number of thinks such set the scroll bars, top and side. some times excel
hicups and looses it reference and UsedRange needs to be reset. a relitive
common post. see this sit on how to reset UsedRange if ever needed....
http://www.contextures.com/xlfaqApp.html#Unused

but to see how much space on a sheet that excel "thinks" has be "used",
run
the following macro..
sub howmuchspace()
activesheet.UsedRange.select
end sub

this will highlight the space that excel "thinks" has been used.

regards
FSt1

"MarkS" wrote:

Hi,
My problem is that if you enter a value in a cell in say row 65,000 and
then
delete it excel will still think the sheet goes to row 65,000. I need to
know
what row and column Excel thinks the sheet goes to.
I've found lots of ways to get it to find the real answer but nothing on
how
to find out what excel thinks it is.

Thanks MarkS