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

  • Thread starter Thread starter Tempy
  • Start date Start date
T

Tempy

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
 
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/lastcell.htm#CleanUpLastCells
'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
 
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,

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)
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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.


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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dave - I should have gone to the link as Otto suggested...
I should remember that (for next time).
Thanks,
JMay

Dave Peterson said:
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.


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 said:
You need to search for something in a cell, not a blank cell ("*").
Click to expand...
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



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return last row no. of column with blanks at top 7
Help with Correct Call for Ron de Bruin's "RDB_Last" to Find Last Row in a Column 1
lastrow function help 6
Function Last Row() 3
Data Cleanse Macro Running Very Slow 0
Inaccurate LAST ROW / LAST COLUMN # 3
Finding last row of every sheet 10
Ron de Bruin - Merge cells from all worksheets 0

Back
Top