reset the data range...
I did a copy of a sheet that has about 20,000 rows to another sheet (
the paste is only values). I then do a sort to bring only the rows that have data in the firs column to tha top. I then search for a cell in the first column that has "Grand Total" as a value. I set this as my starting row and use the last row (Derived using SpecialCells....) to set a range and I delete it . All this works great.. The only problem is that excel still thinks that the last data row is the original last data row even thou ther is nothing there.. what am I doing wrong????? this is the code.. Dim ir As Long, mrows As Long, lastcell As Range, FoundCell As Range Sheets("Projects").Select Cells.Copy Sheets.Add ActiveSheet.Name = "Temp" Selection.PasteSpecial Paste:=xlPasteValues Set lastcell = Cells.SpecialCells(xlLastCell) mrows = lastcell.Row Range("A3:P" & mrows).Select Application.CutCopyMode = False Selection.Sort Key1:=Range("A3"), Order1:=xlAscending Set FoundCell = Cells.Find(What:="Grand Total") Range(FoundCell.Address & ":P" & mrows).Delete End Sub |
reset the data range...
Hi Chris,
See Debra Dalgleish's page on resetting the used range at: http://www.contextures.com/xlfaqApp.html#Unused --- Regards, Norman "Chris Salcedo" wrote in message oups.com... I did a copy of a sheet that has about 20,000 rows to another sheet ( the paste is only values). I then do a sort to bring only the rows that have data in the firs column to tha top. I then search for a cell in the first column that has "Grand Total" as a value. I set this as my starting row and use the last row (Derived using SpecialCells....) to set a range and I delete it . All this works great.. The only problem is that excel still thinks that the last data row is the original last data row even thou ther is nothing there.. what am I doing wrong????? this is the code.. Dim ir As Long, mrows As Long, lastcell As Range, FoundCell As Range Sheets("Projects").Select Cells.Copy Sheets.Add ActiveSheet.Name = "Temp" Selection.PasteSpecial Paste:=xlPasteValues Set lastcell = Cells.SpecialCells(xlLastCell) mrows = lastcell.Row Range("A3:P" & mrows).Select Application.CutCopyMode = False Selection.Sort Key1:=Range("A3"), Order1:=xlAscending Set FoundCell = Cells.Find(What:="Grand Total") Range(FoundCell.Address & ":P" & mrows).Delete End Sub |
All times are GMT +1. The time now is 06:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com