Blank rows at Bottom, over 60,000 of them - how to remove ???
 

I already searched this and found several macros, but they do not
work.

Normally when you have a lot of rows of data - you can click the
scrollbar button and drag down to the bottom to see that last row of
data.

I have a worksheet with data only in Rows 1 to 2500. But when I click
on the scroll button and drag down, it quickly passes the last row of
data and scrolls down way past it - to row 64187. I have deleted all
the blank rows below row 2500, and they still shows up as far as the
scroll button goes.

Any ideas?

Blank rows at Bottom, over 60,000 of them - how to remove ???
 

This routine will search for the first empty row and delete this row and all
rows below it. It appears to set Activesheet.Usedrange correctly and may fix
your scroll problem:

Sub gsnu()
Dim j As Long
Dim i As Long
Dim rr As Range
Dim s As String

Set rr = ActiveSheet.UsedRange

j = rr.Rows.Count + rr.Row

For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
s = i & ":65536"
Rows(s).Delete Shift:=xlUp
Exit Sub
End If
Next i
End Sub
--
Gary's Student


"Kevin" wrote:

I already searched this and found several macros, but they do not
work.

Normally when you have a lot of rows of data - you can click the
scrollbar button and drag down to the bottom to see that last row of
data.

I have a worksheet with data only in Rows 1 to 2500. But when I click
on the scroll button and drag down, it quickly passes the last row of
data and scrolls down way past it - to row 64187. I have deleted all
the blank rows below row 2500, and they still shows up as far as the
scroll button goes.

Any ideas?

Blank rows at Bottom, over 60,000 of them - how to remove ???
 

On Wed, 20 Sep 2006 07:43:04 -0700, Gary''s Student
wrote:

This routine will search for the first empty row and delete this row and all
rows below it. It appears to set Activesheet.Usedrange correctly and may fix
your scroll problem:

Sub gsnu()
Dim j As Long
Dim i As Long
Dim rr As Range
Dim s As String

Set rr = ActiveSheet.UsedRange

j = rr.Rows.Count + rr.Row

For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
s = i & ":65536"
Rows(s).Delete Shift:=xlUp
Exit Sub
End If
Next i
End Sub

I tried it buy luck. I think that sub just deletes blank rows, and I
have tried a few of those including this one now. But it does not
change the way Excel scolls.

I can always scroll all the way to row 65536 using the down arrow.
But the scroll button should only drag the view down to the end of the
data. In this case it drags way past row 2500 which is where my data
ends, and all the way to row 64187.

Blank rows at Bottom, over 60,000 of them - how to remove ???
 

http://www.contextures.com/xlfaqApp.html#Unused

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
On Wed, 20 Sep 2006 07:43:04 -0700, Gary''s Student
wrote:

This routine will search for the first empty row and delete this row and
all
rows below it. It appears to set Activesheet.Usedrange correctly and may
fix
your scroll problem:

Sub gsnu()
Dim j As Long
Dim i As Long
Dim rr As Range
Dim s As String

Set rr = ActiveSheet.UsedRange

j = rr.Rows.Count + rr.Row

For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
s = i & ":65536"
Rows(s).Delete Shift:=xlUp
Exit Sub
End If
Next i
End Sub

I tried it buy luck. I think that sub just deletes blank rows, and I
have tried a few of those including this one now. But it does not
change the way Excel scolls.

I can always scroll all the way to row 65536 using the down arrow.
But the scroll button should only drag the view down to the end of the
data. In this case it drags way past row 2500 which is where my data
ends, and all the way to row 64187.

Blank rows at Bottom, over 60,000 of them - how to remove ???
 

when i have your problem, it is always with a range, formula or a
conditionnal format linked to theses rows
When i correct the error. i close and reopen my workbook and solve
this problem.

jmb

Kevin a écrit :

I already searched this and found several macros, but they do not
work.

Normally when you have a lot of rows of data - you can click the
scrollbar button and drag down to the bottom to see that last row of
data.

I have a worksheet with data only in Rows 1 to 2500. But when I click
on the scroll button and drag down, it quickly passes the last row of
data and scrolls down way past it - to row 64187. I have deleted all
the blank rows below row 2500, and they still shows up as far as the
scroll button goes.

Any ideas?