Solved

Excel 2007 Rows Will Not Delete Manually or with VBA

Posted on 2011-09-06
18
284 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
  • 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now