I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to stay
blank. My main problem is deleteing the extra 65,536 blank rows below my data
that randomly appeared when I copy and pasted my data to a new workbook. When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my scroll
bar had become all but useless- please help! Thanks!

hi
i think excel has lost it's used range reference.
see this site to reset.

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

regards
FSt1

"Kajuliano" wrote:

I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to stay
blank. My main problem is deleteing the extra 65,536 blank rows below my data
that randomly appeared when I copy and pasted my data to a new workbook. When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my scroll
bar had become all but useless- please help! Thanks!

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below my
data
that randomly appeared when I copy and pasted my data to a new workbook.
When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!

Thanks for the website- I tried highlighing the rows and deleting and that
didn't reset the range. I even saved and closed but I have office 2007 so I
don't think that is an issue. They give a macro to programatically reset the
used range but I'm not quite sure how to use the macro-- do I have to put in
values or can I simply compy and paste it without adding or changing
anything? Also, every time I open and close this file, this message pops up :
Compile error in hidden module: DistMon-- think that has anything to do with
it?? Thanks!!

"FSt1" wrote:

hi
i think excel has lost it's used range reference.
see this site to reset.

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

regards
FSt1

"Kajuliano" wrote:

I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to stay
blank. My main problem is deleteing the extra 65,536 blank rows below my data
that randomly appeared when I copy and pasted my data to a new workbook. When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my scroll
bar had become all but useless- please help! Thanks!

Thanks for the reply-- that was the same website FSt1 directed me to as well
and I could not reset the used range using this method for some reason. As I
said in my reply to FSt1 above I am unclear on using the macro to
programatically reset the used range-- any direction in that area would be
helpful thanks!

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below my
data
that randomly appeared when I copy and pasted my data to a new workbook.
When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!

I just tried both methods in an Excel 2007 file and both methods worked.

I'm kind of suspicious of this:

every time I open and close this file, this message pops up
: Compile error in hidden module: DistMon--
think that has anything to do with it??

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
Thanks for the reply-- that was the same website FSt1 directed me to as
well
and I could not reset the used range using this method for some reason. As
I
said in my reply to FSt1 above I am unclear on using the macro to
programatically reset the used range-- any direction in that area would be
helpful thanks!

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650
rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below
my
data
that randomly appeared when I copy and pasted my data to a new
workbook.
When
I highlight a single blank row or many blank rows beneath my data and
hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!

For the Distmon error message.

See KB article
http://support.microsoft.com/default...;EN-US;q307410

The PDFMaker.xla and maybe PDFMaker.dot are stored in your Office\XLSTART
folder or in the Excel\XLSTART folder.

Any files in these folders will open with Excel.

You must remove them from the XLSTART folder.


Gord Dibben MS Excel MVP

On Wed, 22 Oct 2008 23:45:19 -0400, "T. Valko"
wrote:

I just tried both methods in an Excel 2007 file and both methods worked.

I'm kind of suspicious of this:

every time I open and close this file, this message pops up
: Compile error in hidden module: DistMon--
think that has anything to do with it??

Well i copied and pasted all of my data into a new workbook just to make sure
I didn't hit some weird setting in the original one and the highlight delete
method still did not work. Then I copied the macro and ran it and it made it
so my scroll bar actually works well now, but the extra rows are still there.
All that I cared about was the scroll bar working so I am satisfied. As for
the DistMon error I think it has something to do with different Adobe files
that are stored on my computer (I just looked this up). Thank you for the
time and the help!!

"T. Valko" wrote:

I just tried both methods in an Excel 2007 file and both methods worked.

I'm kind of suspicious of this:

every time I open and close this file, this message pops up
: Compile error in hidden module: DistMon--
think that has anything to do with it??

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
Thanks for the reply-- that was the same website FSt1 directed me to as
well
and I could not reset the used range using this method for some reason. As
I
said in my reply to FSt1 above I am unclear on using the macro to
programatically reset the used range-- any direction in that area would be
helpful thanks!

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650
rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below
my
data
that randomly appeared when I copy and pasted my data to a new
workbook.
When
I highlight a single blank row or many blank rows beneath my data and
hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!

Thanks-- I am actually working from a remote desktop connection through my
university and I do not think we have access to change settings but I'm fine
with this little error message, it doesn't bother me much- thanks though!

"Gord Dibben" wrote:

For the Distmon error message.

See KB article
http://support.microsoft.com/default...;EN-US;q307410

The PDFMaker.xla and maybe PDFMaker.dot are stored in your Office\XLSTART
folder or in the Excel\XLSTART folder.

Any files in these folders will open with Excel.

You must remove them from the XLSTART folder.


Gord Dibben MS Excel MVP

On Wed, 22 Oct 2008 23:45:19 -0400, "T. Valko"
wrote:

I just tried both methods in an Excel 2007 file and both methods worked.

I'm kind of suspicious of this:

every time I open and close this file, this message pops up
: Compile error in hidden module: DistMon--
think that has anything to do with it??

Glad to hear you got it to work (one way or the other!).

I copied the macro and ran it and it made it so my
scroll bar actually works well now, but the extra
rows are still there.

Yeah, as I noted in my other reply you're not actually deleting (removing)
the rows/columns. You're just resetting where Excel thinks your data ends
(the used range). The scroll bar operation correlates to the size of the
used range.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
Well i copied and pasted all of my data into a new workbook just to make
sure
I didn't hit some weird setting in the original one and the highlight
delete
method still did not work. Then I copied the macro and ran it and it made
it
so my scroll bar actually works well now, but the extra rows are still
there.
All that I cared about was the scroll bar working so I am satisfied. As
for
the DistMon error I think it has something to do with different Adobe
files
that are stored on my computer (I just looked this up). Thank you for the
time and the help!!

"T. Valko" wrote:

I just tried both methods in an Excel 2007 file and both methods worked.

I'm kind of suspicious of this:

every time I open and close this file, this message pops up
: Compile error in hidden module: DistMon--
think that has anything to do with it??

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
Thanks for the reply-- that was the same website FSt1 directed me to as
well
and I could not reset the used range using this method for some reason.
As
I
said in my reply to FSt1 above I am unclear on using the macro to
programatically reset the used range-- any direction in that area would
be
helpful thanks!

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally
removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650
rows
and there are blank cells within the rows I am working on that need
to
stay
blank. My main problem is deleteing the extra 65,536 blank rows
below
my
data
that randomly appeared when I copy and pasted my data to a new
workbook.
When
I highlight a single blank row or many blank rows beneath my data
and
hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!

Thanks for this info.I used it too.
However I keep getting an error when I run the same script from the link.
the debugger stops here- with an application-defined or object defined errror.

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

Any ideas
Rory

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below my
data
that randomly appeared when I copy and pasted my data to a new workbook.
When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!

I'm guessing that it's the first logical line (first 2 lines of code) that is
causing the trouble.

What does myLastRow equal?

Add
MsgBox MyLastRow
right before this portion of code.

And is there any chance that the worksheet is protected?

rory_r wrote:

Thanks for this info.I used it too.
However I keep getting an error when I run the same script from the link.
the debugger stops here- with an application-defined or object defined errror.

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

Any ideas
Rory

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below my
data
that randomly appeared when I copy and pasted my data to a new workbook.
When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!




--

Dave Peterson

I ran the MSG box and it returned 0
After I run the script and get the error (with the option of running the
debugger), the spread sheets extra rows over 600 are gone. So it seems to
work to that point.

As far as protected, it's not, it probably should be tho.
Rory

"Dave Peterson" wrote:

I'm guessing that it's the first logical line (first 2 lines of code) that is
causing the trouble.

What does myLastRow equal?

Add
MsgBox MyLastRow
right before this portion of code.

And is there any chance that the worksheet is protected?

rory_r wrote:

Thanks for this info.I used it too.
However I keep getting an error when I run the same script from the link.
the debugger stops here- with an application-defined or object defined errror.

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

Any ideas
Rory

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below my
data
that randomly appeared when I copy and pasted my data to a new workbook.
When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!




--

Dave Peterson

One of the things that isn't apparent to me is what the .range() and .cells()
refer to.

There's a "with ..." statement that you haven't shared. Maybe it's not what you
think.

If you're using a variable in that with statement, share what it holds, too.

rory_r wrote:

I ran the MSG box and it returned 0
After I run the script and get the error (with the option of running the
debugger), the spread sheets extra rows over 600 are gone. So it seems to
work to that point.

As far as protected, it's not, it probably should be tho.
Rory

"Dave Peterson" wrote:

I'm guessing that it's the first logical line (first 2 lines of code) that is
causing the trouble.

What does myLastRow equal?

Add
MsgBox MyLastRow
right before this portion of code.

And is there any chance that the worksheet is protected?

rory_r wrote:

Thanks for this info.I used it too.
However I keep getting an error when I run the same script from the link.
the debugger stops here- with an application-defined or object defined errror.

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

Any ideas
Rory

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below my
data
that randomly appeared when I copy and pasted my data to a new workbook.
When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!




--

Dave Peterson


--

Dave Peterson

Dave,
FIrstly, thanks so much for your help.
Here's the entire section.

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
"Dave Peterson" wrote:

One of the things that isn't apparent to me is what the .range() and .cells()
refer to.

There's a "with ..." statement that you haven't shared. Maybe it's not what you
think.

If you're using a variable in that with statement, share what it holds, too.

rory_r wrote:

I ran the MSG box and it returned 0
After I run the script and get the error (with the option of running the
debugger), the spread sheets extra rows over 600 are gone. So it seems to
work to that point.

As far as protected, it's not, it probably should be tho.
Rory

"Dave Peterson" wrote:

I'm guessing that it's the first logical line (first 2 lines of code) that is
causing the trouble.

What does myLastRow equal?

Add
MsgBox MyLastRow
right before this portion of code.

And is there any chance that the worksheet is protected?

rory_r wrote:

Thanks for this info.I used it too.
However I keep getting an error when I run the same script from the link.
the debugger stops here- with an application-defined or object defined errror.

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

Any ideas
Rory

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below my
data
that randomly appeared when I copy and pasted my data to a new workbook.
When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!




--

Dave Peterson


--

Dave Peterson

I don't understand how it blows up on the Else portion of this section:

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

If myLastRow is really 0, then the "then" portion should be followed. That
means this line:

..columns.delete

should be the line that's running.

I don't have a guess.

rory_r wrote:

Dave,
FIrstly, thanks so much for your help.
Here's the entire section.

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
"Dave Peterson" wrote:

One of the things that isn't apparent to me is what the .range() and .cells()
refer to.

There's a "with ..." statement that you haven't shared. Maybe it's not what you
think.

If you're using a variable in that with statement, share what it holds, too.

rory_r wrote:

I ran the MSG box and it returned 0
After I run the script and get the error (with the option of running the
debugger), the spread sheets extra rows over 600 are gone. So it seems to
work to that point.

As far as protected, it's not, it probably should be tho.
Rory

"Dave Peterson" wrote:

I'm guessing that it's the first logical line (first 2 lines of code) that is
causing the trouble.

What does myLastRow equal?

Add
MsgBox MyLastRow
right before this portion of code.

And is there any chance that the worksheet is protected?

rory_r wrote:

Thanks for this info.I used it too.
However I keep getting an error when I run the same script from the link.
the debugger stops here- with an application-defined or object defined errror.

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

Any ideas
Rory

"T. Valko" wrote:

See this:

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

Note that when you "delete" the unused rows you're not literally removing
them from the the sheet. Technically, what you're actually doing is
resetting the *used range*.

--
Biff
Microsoft Excel MVP


"Kajuliano" wrote in message
...
I am working on an excel file in which I currently have data in 1,650 rows
and there are blank cells within the rows I am working on that need to
stay
blank. My main problem is deleteing the extra 65,536 blank rows below my
data
that randomly appeared when I copy and pasted my data to a new workbook.
When
I highlight a single blank row or many blank rows beneath my data and hit
delete row, they do not go away and it is really annoying because my
scroll
bar had become all but useless- please help! Thanks!




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson