Hidden cells

[emma makela]

I have a workbook with tens of columns, thousands of rows and so on, you
all know...

The original 'sheet maker' just copied and pasted all the information
from some kind of a registry. Over the half of the columns and their
data is useless and could be forgotten and even deleted...
But there are macros and all kind of codes I am not aware of and
probably wouldn't understand either, even if I knew where to look...
So I have left the sheets in peace.

But now I have to do some new staff with my own macros and copying and
pasting data and rows....

So what is the simplest/easiest/wisest way to continue?
Is there a way to delete columns without interfering its name? I mean
that the columns would be A, B, AZ, BC for instance...

Or should i give a notice at that miserable place I call job?

Any advice and help and recommendations is taken with greate
gratitude...

 

[IC]

The simplest is to hide the irrelevant columns/rows. This doesn't get rid of
the data, just stops it showing.

Assuming there are no macros, only formulae you could check whether the
columns are referenced by any formulae. To toggle between displaying values
and formulae, press Ctrl + ` (grave accent). Click on each cell with a
formula and you will see the referenced cells highlighted. On such a large
spreadsheet this is likely to take a long time as you will need to scroll
round the sheet each time you check a cell.

Another option is to use the Find facility to find references to a column or
row.
Go to edit and find, then click options. Select Look in: Formulas and click
Match case. In Find what, type the column in capitals and click Find All.
This will give you a list of cells containing that letter. Unfortunately it
will find all references to that capital letter, but by scrolling down, you
can easily see which cells contain a formula and whether the reference is in
that formula.

Assuming there are no formulae referencing a row or column, it can be safely
deleted. As always before deleting data MAKE A BACKUP!

 

[EM]

Hi there, and thanks for answering.

Cells that are hidden doesn't have any relevant information at all.
But...the workbook is copied into other places and there are macros and
formulas and who knowe what in those useful cells. So if I deleted a column
But it disappears all right, but then column C moves itself to be a column
B...and that messes up everything....
So I'm still looking for a solution....

-Thanks E-

 

[IC]

Unfortunately you have a difficult problem. As you say, if you delete column
B, columns C, D etc change their reference. Thsi sin't a problem with a
formula in a cell, even an absolute reference will change (eg $C$1 will
become $B$1).

The problem lies in the macros/VBA. These will not change to reflect the
changing layout so you will have to edit these manually. You will be able to
use the Replace function in the VB editor, but it will still be a long
process, especially as you have many columns and rows. I'm afraid I can't
think of an easy way round it. You may find it easier to print out the
existing code, decide which rows & columns are to be deleted then work out
what needs to be changed (eg if you delete columns B & F, then all
references to C, D & E must be changed to B,C & D (1 column change) and G, H
etc to E, F (2 culumn change)).

If you make a copy of your original file, then work on the copy, you will be
able to compare the values in your modified sheet to check your work.