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

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