Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find next blank column in set area | Excel Programming | |||
Comments - display on dirty | Excel Programming | |||
excel sheet dirty read and dirty write? | Excel Programming | |||
using Dirty Method | Excel Programming | |||
How to tell if a range or cell is dirty | Excel Programming |