Deleting Rows

[Guest]

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.

 

[merjet]

Use End+(up or down arrow) instead?

Hth,
Merjet

 

[Tom Ogilvy]

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.

 

[Guest]

For whatever reason, Excel doesn't 'reset' the usedrange until you save and
reopen the workbook...

I'm trying to 'reset' it programatically.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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