MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Clear Contents Not Working

  Asked By: Cody    Date: Jan 16    Category: MS Office    Views: 997

I need to delete out a range of rows in my spreadsheet because I am
going to use it linked to an Access database. When I link into the database
all these blank records show.

So, I thought it would be an easy fix. I opened the spreadsheet, located
the last cell by pressing CTRL+End which took me to cell C1319. I then
selected the range from A1182:C1319 and have tried the following to get the
spreadsheet to recognize that C1181 is the last used cell:
- Deleted rows
- Edit, Clear, All
- Pressed the Delete key

All to no avail. What other step am I missing?



5 Answers Found

Answer #1    Answered By: Monique Perry     Answered On: Jan 16

I've found saving and closing the file after deleting (I delete  entire
rows (with right-click|Delete) having selected them with their row
numbers) usually resets the LastCell location.
Otherwise use something like:

Answer #2    Answered By: Roosevelt Jenkins     Answered On: Jan 16

What I would suggest to do is Fix a cell  in the Spreadsheet which will count
the Number of Rows you have and use that cell as EOF

Supposing you have Heading from Column A : I and Column A contains the
Unique Code then give the formula in J1 as counta(a2:a65636).
This will keep track of the number of Records you have. The same can be
used to define the END OF FILE (EOF)

Answer #3    Answered By: Rosie Hughes     Answered On: Jan 16

Thanks, that would work if I were going to be coding in the spreadsheet,
however, what I needed to do was use the data as a linked  file inside of
Access. I ended up copying the pertinent data to another spreadsheet  and
using that as my linked table.

It's a bummer that Clear Contents did not re-set the "used range" like it
should have.

Answer #4    Answered By: Freya Brown     Answered On: Jan 16

i've had similar problems and i solved it by selecting the row following the
data and then selecting all rows  until the end... delete  them using the edit
menu.. then save it.. then I check for last row by using control + end.

i've only had this fail me once, and that ended up being a file that once
had a virus that was cleaned but something was still messed up so I just
created new workbook and copied data over to the new book.

Answer #5    Answered By: Dep Tran     Answered On: Jan 16

I guess I had the same virus, because selecting the blank rows  and deleting
didn't fix  this file, I ended up creating a new spreadsheet.

Didn't find what you were looking for? Find more on Clear Contents Not Working Or get search suggestion and latest updates.