Solved

Excel 2007 Rows Will Not Delete Manually or with VBA

Posted on 2011-09-06
18
291 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

911 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

20 Experts available now in Live!

Get 1:1 Help Now