Solved

Excel 2007 Rows Will Not Delete Manually or with VBA

Posted on 2011-09-06
18
320 Views
Last Modified: 2012-06-27
Hey experts,

Note: I am using Excel 2007.  I have a spreadsheet that I import, which currently contains 65536 rows (max rows in Excel 2003?).  The spreadsheet is an .xls file, and only contains data in the first ~1000 rows.  New data gets added to the spreadsheet each week, so the rows of data will continually grow, but should never grow more than 2000 rows total.

My issue is that the spreadsheet contains 65536 rows total, but data is only in the first ~1000 rows.  I have tried to manually delete the rows that do not contain data, as well as deleting them via VBA.  Either way, the rows do not get deleted... It's like there is a lock on the total number of rows, declaring that there must be 65536 rows at all times.  Could someone enlighten me on this?

Thanks,
Rob
0
Comment
Question by:RobStl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
18 Comments
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36490161
If you click Ctrl+End keys together,  which cell gets the focus?
0
 

Author Comment

by:RobStl
ID: 36490178
Strangely, cell BH 64891 gets focused.
BH makes sense, it is the last column used.
Row 64891 makes no sense at all...
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36490212
There is some data there. Please select the row and delete it and then retry Ctrl+End.
 It will be interesting see which cell gets focus now. You can repeat the above steps until that last record of your desired data range gets the focus OR if it is possible, please share the workbook (it would be helpful to find out what's wrong).
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:peetjh
ID: 36490222
I'm sure you have already tried but after you delete the rows you need to save and re open the file to see the new end of the file.
0
 

Author Comment

by:RobStl
ID: 36490261
Thank you for your reply.  However, I still have the same issue...

I have tried doing this (deleting all of the rows past the obvious data, either manually or with VBA).  So I delete the rows, press Ctrl + End again, and it still says that my last cell is BH64981.  

Also, I cannot share the worksheet, unless I fill in 1000 rows X 60 columns with metadata...  I'll attempt to do that now, but I doubt it will fix the issue.  I'll reply again once I have entered all the fake data.
0
 
LVL 14

Expert Comment

by:peetjh
ID: 36490283
Just to be clear are you saying you cannot save the workbook without the fake data? I don't believe sharing it is going to resolve anything.
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36490322
Thank you RobStl.
The behavior is strange indeed, I'm curious to see if the is any code(triggered by an event) that's pouplating that particular cell. Why don'yt you just delete the first 1000 lines and then share the file?
0
 

Author Comment

by:RobStl
ID: 36490327
Well, I saved and re-opened the file, and the new end of file was different.  Currently, BH 62254.

However, even though it says that this is the end of my file, there are still rows after 62254 all the way until 65536.  So, even though it says that this is the end of my file, it is annoying when scrolling through the file because it still scrolls all the way down to 65536.

My point being that even though row 62254 or 64891 or whatever, is my "last record", rows are not being "deleted".  When you scroll to the bottom of the spreadsheet, it scrolls all the way down to 65536.  I'm still updating my spreadsheet so that I can upload it soon..
0
 

Author Comment

by:RobStl
ID: 36490363
Attached is the empty spreadsheet. RowIssueExample.xls
0
 
LVL 14

Accepted Solution

by:
peetjh earned 250 total points
ID: 36490378
After I open your file and run the code rows(1000 & ":" & 65536).delete(xlup)
and save the file when I open the file my last row is 999.
0
 

Author Comment

by:RobStl
ID: 36490424
So strange....

So I did the same exact thing, it deleted the rows while I had the spreadsheet open.  My last row was 999.  Then I saved it, closed it, re-opened it, and my last row was 61386. =(
0
 
LVL 14

Expert Comment

by:peetjh
ID: 36490441
I tried saving and re-opening multiple times and cannot duplicate what you are seeing.
0
 
LVL 14

Expert Comment

by:peetjh
ID: 36490446
Do you have any code that runs when the file is opened that could cause this?
0
 
LVL 14

Expert Comment

by:peetjh
ID: 36490459
Did you try the removal and save with the example file? How does it react?
0
 
LVL 10

Assisted Solution

by:SANTABABY
SANTABABY earned 250 total points
ID: 36490464
This is what I did:
Ctrl + end to go the the last record.
Selecet the row
Move your cursor up to row 1000 and shift+select the rpw, it will select the whole range in between.
Delete the selected rows
Save the file.
After reopening, I saw that the last row is 999.
Attached is the file.
RowIssueExample.xls
0
 

Author Comment

by:RobStl
ID: 36490524
I guess I should assume that my Excel is broken. =/  That's kind of what I've been trying to say...  No matter what, it won't delete the rows...

Even when I do what you said, then hit Ctrl+End after running the code, and it says BH 999 is the last used cell.  I save, close, re-open, and rows still exist below row 999.  It's like they won't delete...

Oh well, I'll have to keep it as is for now...  I'll just assume that my Excel is broken.  Thank you all for your help.
0
 

Author Comment

by:RobStl
ID: 36490548
Yep, I have been trying everything on the example file.. I also tried what you said Santababy.  It's like Excel is trying to delete the contents of the rows, rather than deleting the rows themselves.
0
 

Author Comment

by:RobStl
ID: 36490623
Also, there is not any code that is ran when opening the file.

Just an FYI, don't know if this helps... but I saved the example file as an .xlsx file to see what would happen, and the file now has the full ~1 million rows that are supported in an Excel 2007 file.  I did not add any rows or anything..  It's like my Excel application autopopulates my spreadsheets with the maximum number of rows. =/
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question