Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with rest of forumla - Delete rows based on criteria | Excel Discussion (Misc queries) | |||
how to select the first character in a cell and delete the rest | Excel Worksheet Functions | |||
how to keep the first initial of a name &delete the rest in cell | Excel Discussion (Misc queries) | |||
Can I lock data into few cells in sheet and clear the rest | Excel Discussion (Misc queries) | |||
Select certain rows of sheet & delete the rest | Excel Programming |