Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing old data from a sheet
Hi I need to clear all data that may be on a sheet except for the firs two rows. I have tried to use: EXCEL::_WORKSHEETPTR OBJSHEET; .... EXCEL::RANGEPTR RANGE = NULL; RANGE = OBJSHEET-GETUSEDRANGE(); LONG LROW = (RANGE-GETEND(XLDOWN))-GETROW(); LONG LCOL = (RANGE-GETEND(XLTORIGHT))-GETCOLUMN() to get the extents of the used range but in some instances GetRow( returns 65536 which causes my code to puke. Anyone have a better way of doing this? Is there any useful (thi automatically excludes MSDN) information out there on this sort o thing? Thank -- 5lmustan ----------------------------------------------------------------------- 5lmustang's Profile: http://www.excelforum.com/member.php...fo&userid=1548 View this thread: http://www.excelforum.com/showthread.php?threadid=27068 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing old data from a sheet
5lmustang To keep your code simple you could use Rows("2:" & Rows.Count).ClearContents or Rows("2:" & Rows.Count).delete To get last used row and column try GetBottomRow = TheSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row LastColumn = TheSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns _ SearchDirection:=xlPrevious).Column To reset the last used row and column that excel thinks is used to wha is actually used try Sub ResetLastRow_n_Column() Dim i As Integer i = ActiveSheet.UsedRange.Offset(X).Resize(1).Row - 1 i = ActiveSheet.UsedRange.Offset(X).Resize(1).Column - 1 End Su -- mudrake ----------------------------------------------------------------------- mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247 View this thread: http://www.excelforum.com/showthread.php?threadid=27068 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
Clearing Data | Excel Discussion (Misc queries) | |||
clearing data from excell sheet | Excel Discussion (Misc queries) | |||
VBA Question / Clearing out All Data | Excel Worksheet Functions | |||
macro? clearing/ transferring info to another sheet | Excel Programming |