Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Excel 2007 Rows Will Not Delete Manually or with VBA

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
RobStl
Asked:
RobStl
  • 8
  • 6
  • 4
2 Solutions
 
SANTABABYCommented:
If you click Ctrl+End keys together,  which cell gets the focus?
0
 
RobStlAuthor Commented:
Strangely, cell BH 64891 gets focused.
BH makes sense, it is the last column used.
Row 64891 makes no sense at all...
0
 
SANTABABYCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JPIT DirectorCommented:
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
 
RobStlAuthor Commented:
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
 
JPIT DirectorCommented:
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
 
SANTABABYCommented:
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
 
RobStlAuthor Commented:
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
 
RobStlAuthor Commented:
Attached is the empty spreadsheet. RowIssueExample.xls
0
 
JPIT DirectorCommented:
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
 
RobStlAuthor Commented:
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
 
JPIT DirectorCommented:
I tried saving and re-opening multiple times and cannot duplicate what you are seeing.
0
 
JPIT DirectorCommented:
Do you have any code that runs when the file is opened that could cause this?
0
 
JPIT DirectorCommented:
Did you try the removal and save with the example file? How does it react?
0
 
SANTABABYCommented:
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
 
RobStlAuthor Commented:
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
 
RobStlAuthor Commented:
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
 
RobStlAuthor Commented:
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now