Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove excess (unwanted) rows from the bottom of a speadsheet | Excel Discussion (Misc queries) | |||
Remove Blank and Non Data Rows | Excel Discussion (Misc queries) | |||
Need macro to remove blank rows | Excel Programming | |||
How do I delete blank rows at the bottom of a spreadsheet to get . | Excel Discussion (Misc queries) | |||
Remove blank rows from combobox | Excel Programming |