MS Excel XP: working with hidden rows and columns
Thread poster: Samuel Murray

Samuel Murray  Identity Verified
Netherlands
Local time: 16:47
Member (2006)
English to Afrikaans
+ ...
Apr 29, 2009

G'day everyone

An evil thing is rearing its head -- clients sending Excel files with hidden columns and rows. With a previous client I simply unhid everything and sent it back, translated. With a recent client, the client specifically asked that I do not unhide hidden columns and rows. This is bad, because it means I'm forced to use Excel to translate Excel.

If a column has hidden rows, and I simply select it, copy it, and paste it in the adjecent column, some of the text gets pasted into hidden rows. This does not make sense -- I mean, to my mind, if I only copied visible cells, the paste action should only affect visible cells... but the developers of Excel thought different, and there you have it.

Another problem with hidden cells is that I have no idea whether they contain any content. If I knew for a fact that none of the hidden cells contained anything, then I could easily use something like OmegaT to extract the text and re-insert the translation. But if the hidden cells do contain stuff, then I'd have no way of knowing whether an extracted cell is hidden or not and I may end up translating it.

So... questions:

1. Is there a way to unhide all hidden cells using a single or simple action, and then rehide all previously hidden cells again using a single or simple?
2. Is there a way to ensure that when I copy a column that contains hidden rows, the hidden rows are included in the copy action (i.e. if they're empty, they should turn up as blank rows).
3. Is there a way to copy a column of cells in such a way that the row number is somehow included in the copy action?

Thanks!


 

Heinrich Pesch  Identity Verified
Finland
Local time: 17:47
Member (2003)
Finnish to German
+ ...
Don't think so Apr 30, 2009

Probably the client wants you to use a CAT-tool that only sees non-hidden cells. I don't believe you can make Excel remember what was once hidden and what not.

I had this problem once with a client. The wordlist was partly translated and the translated rows hidden. When I tried to use my usual procedure by copying into Word and translating there using Wordfast I run into trouble. The client said no other translator had ever complained. That they all translated straight in Excel. I felt almost guilty.

Regards
Heinrich


 

Antoní­n Otáhal
Local time: 16:47
Member (2005)
English to Czech
+ ...
I'm puzzled Apr 30, 2009

Hi,

First of all, I normally use Transit, which lets me decide whether I do or do not want to translate in hidden cells as a matter of course. I thought Trados and other CAT tools also enabled that, but I cannot be sure.

Second, what is the problem in the workflow: "unhide-translate-hide"?

Should the file be big and "manual" remembering of what was originally hidden be impracticable (though some highlighting would probably ensure that unless the file is very big), there is the "Hidden" property in VBA...

Or are there any catches I am unable to realise at the moment?

Antonin


 

Tomás Cano Binder, BA, CT  Identity Verified
Spain
Local time: 16:47
Member (2005)
English to Spanish
+ ...
Do you have to translate the hidden rows? Apr 30, 2009

I just wanted to make sure that you DON'T have to translate the hidden rows.

If you don't have to translate the hidden rows, Trados' TagEditor lets you work on the visible cells only, so it is quite handy.


 

tectranslate ITS GmbH
Local time: 16:47
German
+ ...
Agree with Antonin & Co., but... Apr 30, 2009

...you don't even have to hide-translate-unhide the cells.

If you want to translate everything, including the hidden cells, just activate "Translate content from hidden rows and columns" for Excel files in the SDL Trados Filter Settings applet.

HTH,

Benjamin

edit: ah, you don't have Trados.

[Edited at 2009-04-30 09:46 GMT]


 

Samuel Murray  Identity Verified
Netherlands
Local time: 16:47
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
Yesterday's text... Apr 30, 2009

Heinrich Pesch wrote:
When I tried to use my usual procedure by copying into Word and translating there using Wordfast I run into trouble. The client said no other translator had ever complained. That they all translated straight in Excel. I felt almost guilty.


Copying cells into Word (and using WFC) is my method too! It doesn't work if there are merged cells, however, but in the case of merged cells I'm able to use OmegaT, with some gymnastics. But hidden cells... eish!

And yesterday's text contained hundreds of (what seemed to be) 75-90% matches, so not using a CAT tool would simply not have made any sense. I pity the translators who translate such a file using Excel itself.

Anyway, it turns out that WFC's normal Excel method does skip hidden cells (you know, the method in which you have both Word and Excel open, and you press Alt+down in the empty Word file).


 

Samuel Murray  Identity Verified
Netherlands
Local time: 16:47
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
Even if not so big... Apr 30, 2009

Antoní­n Otáhal wrote:
Should the file be big and "manual" remembering of what was originally hidden be impracticable (though some highlighting would probably ensure that unless the file is very big)...


Even if this file was small with very few hidden cells, the fact that the client specifically told me to keep hidden cells hidden tells me that having hidden cells is possibly part of his normal workflow, and if he should ever send me another job, that next job may well be a bigger one.

When I get a new client who uses a weird method, I always think of the future... the future with the (hopefully by that time a regular) client.


 

Boyan Brezinsky  Identity Verified
Bulgaria
Local time: 17:47
English to Bulgarian
+ ...
Some belated answers May 6, 2009

Samuel Murray wrote:

........
So... questions:

1. Is there a way to unhide all hidden cells using a single or simple action, and then rehide all previously hidden cells again using a single or simple?
2. Is there a way to ensure that when I copy a column that contains hidden rows, the hidden rows are included in the copy action (i.e. if they're empty, they should turn up as blank rows).
3. Is there a way to copy a column of cells in such a way that the row number is somehow included in the copy action?

Thanks!
I'm chiming in a little late, but I thought I'd throw in my 2 cents.
1 - Depends what the ultimate purpose is. With hidden columns I simply select the two columns on the sides of the hidden column(s) and then click 'Unhide' from the menu. I thought I could unhide both rows and columns in one operation by selecting the whole worksheet, but it didn't work (at least in Excel 2000). So I had to perform both operations - Format -> Row -> Unhide and Format -> Column ->Unhide. BTW, there is 'Unhide' in the context menu, when the whole sheet is selected, but it unhides only rows, not columns.
This answers only the first part of the question. I deal with the second part, rehiding, by simply not saving the changes to the worksheet. Which may very well not work in your case, that's why I said it depends on the end purpose.
As it seems that your purpose is to be able to use WF on the table, I don't think this can help much. Unless you are willing to write a macro that reads the 'hidden' flag from all the rows and columns in the worksheet and stores it somewhere. So then you can uhide everything, do the job and at the end feed that data to another macro that hides the rows and columns back. Writing these macros is not a big deal, but is definitely annoying. Check the help topic on the "Hidden" property in the MS Excel Visual Basic Reference in the Help.
2 - I just tried that in Excel and the hidden rows WERE copied along to another place in the same worksheet, no matter the content. And they stayed hidden after copying. However, when copying from Excel to Notepad, the hidden rows did not get copied at all. I suppose the same applies to copying from Excel to Word, so WF can't be used properly.
3 - Either you write a macro. Or you add a column filled with ROW(). From the help:
ROW
Returns the row number of a reference.
Syntax
ROW(reference)
Reference is the cell or range of cells for which you want the row number.
If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears.
If reference is a range of cells, and if ROW is entered as a vertical array, ROW returns the row numbers of reference as a vertical array.
Reference cannot refer to multiple areas.

Examples
ROW(A3) equals 3
When entered as an array formula in three vertical cells:
ROW(A3:B5) equals {3;4;5}
If ROW is entered in C5, then:
ROW() equals ROW(C5) equals 5


 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

MS Excel XP: working with hidden rows and columns

Advanced search






PerfectIt consistency checker
Faster Checking, Greater Accuracy

PerfectIt helps deliver error-free documents. It improves consistency, ensures quality and helps to enforce style guides. It’s a powerful tool for pro users, and comes with the assurance of a 30-day money back guarantee.

More info »
SDL MultiTerm 2019
Guarantee a unified, consistent and high-quality translation with terminology software by the industry leaders.

SDL MultiTerm 2019 allows translators to create one central location to store and manage multilingual terminology, and with SDL MultiTerm Extract 2019 you can automatically create term lists from your existing documentation to save time.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search