Go to last row of data and delete the rest to bottom of s/sheet
 

Could somebody help me with some code to perform this.

I have a spread sheet with a varying amount of rows.

It gets updated daily and i run some code to do various things. I need
to go down to the last row with data in and then delete the rest. I
think that when the spreed sheet is set up (by somebody else), they set
the area as when i use some other code that i found to go to the bottom
of data it finds more than what there is?!
The code below is what i use to see how many rows.

myRows = last
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox last

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Go to last row of data and delete the rest to bottom of s/sheet
 

Instead of that try this. I had the same problem with imported sheets being
huge with blank space, this fixed them.

Sub Reset_all_lastcells()
'2002-08-02 based, David McRitchie, programming
' http://www.mvps.org/dmcritchie/excel...eanUpLastCells
'This macro will attempt to reset internals, based on a little trick
'involving usedrange.rows.count which may or may not work
'but would be nondestructive.
Application.Calculation = xlCalculationManual
Dim xlong As Long, cSht As Long
For cSht = 1 To ActiveWorkbook.Worksheets.Count
Worksheets(cSht).Select
xlong = ActiveSheet.UsedRange.Rows.Count + _
ActiveSheet.UsedRange.Columns.Count 'Tip73
Next cSht
ActiveWorkbook.Save
AbortCode:
'-- one of these is only done in macros make sure you exit thru here...
Application.Calculation = xlCalculationAutomatic

End Sub

Mike F

"Tempy" wrote in message
...
Could somebody help me with some code to perform this.

I have a spread sheet with a varying amount of rows.

It gets updated daily and i run some code to do various things. I need
to go down to the last row with data in and then delete the rest. I
think that when the spreed sheet is set up (by somebody else), they set
the area as when i use some other code that i found to go to the bottom
of data it finds more than what there is?!
The code below is what i use to see how many rows.

myRows = last
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox last

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Go to last row of data and delete the rest to bottom of s/sheet
 

You need to search for something in a cell, not a blank cell ("*"). Here is
a macro from Debra Dalgliesh that resets the last cell of the sheet, rows
and columns both. HTH Otto
'This code is from Debra Dalgliesh at:
'http://www.contextures.on.ca/xlfaqApp.html#Unused
Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
End Sub



"Tempy" wrote in message
...
Could somebody help me with some code to perform this.

I have a spread sheet with a varying amount of rows.

It gets updated daily and i run some code to do various things. I need
to go down to the last row with data in and then delete the rest. I
think that when the spreed sheet is set up (by somebody else), they set
the area as when i use some other code that i found to go to the bottom
of data it finds more than what there is?!
The code below is what i use to see how many rows.

myRows = last
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox last

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Go to last row of data and delete the rest to bottom of s/sheet
 

Tempy,

That doesn't make sense. If you go to the bottom, why would you want to
delete the rest, there is nothing to delete.

This will get you to the last row in column A

Cells(Rows.Count,"A").End(xlUp).Select

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tempy" wrote in message
...
Could somebody help me with some code to perform this.

I have a spread sheet with a varying amount of rows.

It gets updated daily and i run some code to do various things. I need
to go down to the last row with data in and then delete the rest. I
think that when the spreed sheet is set up (by somebody else), they set
the area as when i use some other code that i found to go to the bottom
of data it finds more than what there is?!
The code below is what i use to see how many rows.

myRows = last
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox last

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Go to last row of data and delete the rest to bottom of s/sheet
 

Otto:
After running this macro I found that you must Close the file (and answer
"Yes" to the q Do you wish to save?) and then re-open to have the
Control-end actually goto the last-used cell. Could the Close/re-open be
included in the original macro?
TIA,

"Otto Moehrbach" wrote in message
...
You need to search for something in a cell, not a blank cell ("*"). Here
is
a macro from Debra Dalgliesh that resets the last cell of the sheet, rows
and columns both. HTH Otto
'This code is from Debra Dalgliesh at:
'http://www.contextures.on.ca/xlfaqApp.html#Unused
Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
End Sub



"Tempy" wrote in message
...
Could somebody help me with some code to perform this.

I have a spread sheet with a varying amount of rows.

It gets updated daily and i run some code to do various things. I need
to go down to the last row with data in and then delete the rest. I
think that when the spreed sheet is set up (by somebody else), they set
the area as when i use some other code that i found to go to the bottom
of data it finds more than what there is?!
The code below is what i use to see how many rows.

myRows = last
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox last

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Go to last row of data and delete the rest to bottom of s/sheet
 

Debra documents this on that link:

4. Save the file. Note: In older versions of Excel,
you may have to Save, then close and re-open
the file before the used range is reset.



JMay wrote:

Otto:
After running this macro I found that you must Close the file (and answer
"Yes" to the q Do you wish to save?) and then re-open to have the
Control-end actually goto the last-used cell. Could the Close/re-open be
included in the original macro?
TIA,

"Otto Moehrbach" wrote in message
...
You need to search for something in a cell, not a blank cell ("*"). Here
is
a macro from Debra Dalgliesh that resets the last cell of the sheet, rows
and columns both. HTH Otto
'This code is from Debra Dalgliesh at:
'http://www.contextures.on.ca/xlfaqApp.html#Unused
Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
End Sub



"Tempy" wrote in message
...
Could somebody help me with some code to perform this.

I have a spread sheet with a varying amount of rows.

It gets updated daily and i run some code to do various things. I need
to go down to the last row with data in and then delete the rest. I
think that when the spreed sheet is set up (by somebody else), they set
the area as when i use some other code that i found to go to the bottom
of data it finds more than what there is?!
The code below is what i use to see how many rows.

myRows = last
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox last

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



--

Dave Peterson

Go to last row of data and delete the rest to bottom of s/sheet
 

Thanks Dave - I should have gone to the link as Otto suggested...
I should remember that (for next time).
Thanks,
JMay

"Dave Peterson" wrote in message
...
Debra documents this on that link:

4. Save the file. Note: In older versions of Excel,
you may have to Save, then close and re-open
the file before the used range is reset.



JMay wrote:

Otto:
After running this macro I found that you must Close the file (and
answer
"Yes" to the q Do you wish to save?) and then re-open to have the
Control-end actually goto the last-used cell. Could the Close/re-open
be
included in the original macro?
TIA,

"Otto Moehrbach" wrote in message
...
You need to search for something in a cell, not a blank cell ("*").
Here
is
a macro from Debra Dalgliesh that resets the last cell of the sheet,
rows
and columns both. HTH Otto
'This code is from Debra Dalgliesh at:
'http://www.contextures.on.ca/xlfaqApp.html#Unused
Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
End Sub



"Tempy" wrote in message
...
Could somebody help me with some code to perform this.

I have a spread sheet with a varying amount of rows.

It gets updated daily and i run some code to do various things. I
need
to go down to the last row with data in and then delete the rest. I
think that when the spreed sheet is set up (by somebody else), they
set
the area as when i use some other code that i found to go to the
bottom
of data it finds more than what there is?!
The code below is what i use to see how many rows.

myRows = last
On Error Resume Next
last = ActiveSheet.Cells.Find(What:="", _
After:=[A1], _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox last

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



--

Dave Peterson