[ExcelVBA] Re: Clearing the cells that have no links to them.

 

Hi

Your Problem: delete everything on SheetX that is not referenced from
another sheet in the same workbook.

1) I think what you are trying to do is impractical (not necessarily
impossible) except in very narrow circumstances.

For instance:

a. on sheet1, A1 is "10" and B1 is "=A1"

b. on sheet 2 A1 = "Sheet1!$B$1"

result... you would delete A1 because it wasn't referenced from
another sheet, but that would destroy the value in B1.

2) There is no automatic way of doing this, it needs manual or vba
processing

3) Assuming you don't have the issues of the type in (1) , you need
to:

a. search all sheets and the name space for formula references to
"sheetX!"

i.
Manually (for sheets) Find All "sheetX!" (options, within "workbook",
look in "formulas")

ii.
Manually(names) open "name manager" and filter for "workbook scope names"
and find those with "sheetX!"

b. Union all their ranges together

c. Delete the content of every used cell not in that union.

4) The tabulation, union, and deletion could also be done via VBA

Db

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (4)

Recent Activity:

• New Members 6

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___