Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
I import data from an external source into a workbook. Thru a macro, I sift
thru the information and delete unwanted entries. I start out with 1900 rows of info, after deleting unwanted information, I am left with 100 rows. After deleting the information, how do I 'reset' Excel to put me at the last row used (by the good data) when I press CTRL+End? When I press CTRL+End now, it takes me to the last row/column that ALL the data used. I tried setting by using ActiveWorkbook.Saved = True, but this did not alleviate the problem. I do not want to save the workbook, then reopen it. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Use End+(up or down arrow) instead?
Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Something along the line of
With Activesheet .Range("101:65536").EntireRow.Delete .Range("M:IV").EntireColumn.Delete .UsedRange End With Might work. Adjust to match your situation. -- Regards, Tom Ogilvy "Posse John" wrote in message ... I import data from an external source into a workbook. Thru a macro, I sift thru the information and delete unwanted entries. I start out with 1900 rows of info, after deleting unwanted information, I am left with 100 rows. After deleting the information, how do I 'reset' Excel to put me at the last row used (by the good data) when I press CTRL+End? When I press CTRL+End now, it takes me to the last row/column that ALL the data used. I tried setting by using ActiveWorkbook.Saved = True, but this did not alleviate the problem. I do not want to save the workbook, then reopen it. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
For whatever reason, Excel doesn't 'reset' the usedrange until you save and
reopen the workbook... I'm trying to 'reset' it programatically. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Try this - a lot longer than the previous suggestion:
Sub RemoveEmptyCellsFromUsedRange(i1) ' Control end sometimes gives wrong value so need to remove empty columns and rows ' Remove empty columns on right hand side of worksheet Dim r As Range, c As Range Set r = Worksheets(i1).UsedRange numcols = r.Columns.Count For j = numcols To 1 Step -1 N = 0 For Each c In r.Columns(j).Cells If Trim(c) < "" Then N = N + 1 Next If N = 0 Then Worksheets(i1).Columns(j).Delete Shift:=xlToLeft Else GoTo RowsSt End If Next RowsSt: numrows = r.Rows.Count Application.Calculation = xlManual Application.ScreenUpdating = False For j = numrows To 1 Step -1 N = 0 If j Mod 1000 = 0 Then ' Cells(j, 1).Select MyMsg = "Row " & j ie = MsgBox(MyMsg, vbOKCancel, "Deleting blank rows") If ie = vbCancel Then MsgBox "Exiting routine" Application.Calculation = xlAutomatic Application.ScreenUpdating = True Exit Sub End If End If For Each c In r.Rows(j).Cells c = Trim(c) If c < "" Then N = N + 1 Next If N = 0 Then Worksheets(i1).Rows(j).Delete Shift:=xlShiftUp Else ' once found data don't delete any more blank rows. Cells(j, 1).Select ' MsgBox "Finished" Application.Calculation = xlAutomatic Application.ScreenUpdating = True Exit Sub End If Next Application.Calculation = xlAutomatic Application.ScreenUpdating = True End Sub "Posse John" wrote: For whatever reason, Excel doesn't 'reset' the usedrange until you save and reopen the workbook... I'm trying to 'reset' it programatically. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Are you using excel 97. If not, then I use this all the time and it works.
If it doesn't work for you, then try saving the workbook after you do what I suggested but not closing and opening it. If that doesn't work, then I guess you will have to close and open it, but that hasn't been my experience. Gleam is providing some slow code to discover the rows and columns to be deleted, but never actually resets the usedrange. I assumed you already knew that and emphasized the fact that you actually need to delete entire rows and columns that were unused before resetting the range. Debra Dalgleish has faster code to do this: http://www.contextures.com/xlfaqApp.html#Unused Note that she advocates saving the workbook to reset the range, but as I said, I haven't found that to be necessary in later versions of excel if you executed the command Activesheet.usedrange after deleting entire rows and columns of unused space. -- Regards, Tom Ogilvy "Posse John" wrote: For whatever reason, Excel doesn't 'reset' the usedrange until you save and reopen the workbook... I'm trying to 'reset' it programatically. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
I agree that my code is slow, and look forward to trying Tom's suggestion.
I am surprised that saving a file resets the used range as in my experience on opening a file, "used range" gives the wrong values. "Tom Ogilvy" wrote: Are you using excel 97. If not, then I use this all the time and it works. If it doesn't work for you, then try saving the workbook after you do what I suggested but not closing and opening it. If that doesn't work, then I guess you will have to close and open it, but that hasn't been my experience. Gleam is providing some slow code to discover the rows and columns to be deleted, but never actually resets the usedrange. I assumed you already knew that and emphasized the fact that you actually need to delete entire rows and columns that were unused before resetting the range. Debra Dalgleish has faster code to do this: http://www.contextures.com/xlfaqApp.html#Unused Note that she advocates saving the workbook to reset the range, but as I said, I haven't found that to be necessary in later versions of excel if you executed the command Activesheet.usedrange after deleting entire rows and columns of unused space. -- Regards, Tom Ogilvy "Posse John" wrote: For whatever reason, Excel doesn't 'reset' the usedrange until you save and reopen the workbook... I'm trying to 'reset' it programatically. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |