Hidden Column Key

[Tudball]

Hello,

I am looking to add into an Excel document a hidden column (probably A) consisting of values from 1 to n-1, where n is the number of rows (it'll be starting in row two). This way, if one of the many people accessing the document reorganises it in alphabetical order, for example, the hidden column could be revealed and the document could be reshuffled back to its original order (from the smallest value in column A to the largest value).

But I also need a macro that, when a new row is entered, the hidden column automatically adds a new value that is one greater than the last.

I hope that this makes sense. If there is a more efficient way to achieve this end, please let me know.

Thanks,
Tudball

[patel45]

you can use the function =ROW() in column A

[HaHoBe]

Hi. patel45,

to my opinion not a good hint at all. Row is adapting to each sorting and will always display the actual row number no matter on what row the information was in before.

If you donīt understand a question correctly it might be better not to answer (trust me: I know about the difficulties with a foreign language, and I keep my posting poll closed if I donīt understand).

@Tudball,
If users reorder/sort how do you think you can make sure that they include your key column if itīs in column A? To my knowledge there is no event being triggered when a row /column is inserted or deleted. You could go by checking if an entire row/column has been marked.

Ciao,
Holger

[patel45]

Hi. patel45,
If you donīt understand a question correctly it might be better not to answer

I did understand well, but I was wrong, I'm human, but I'm calm because I know you control me and correct me, so I can learn

[Tudball]

Hello,

Thank you both for your interest.

I have filter options at the top of each column, so that the rows can be reorganised for ease of access, and then a filter function for Column A can be set from smallest to largest, and the rows revert to their original setting.

I could ask the users to manually update Column A each time a new organisation is added, but I would prefer that being done automatically.

Could there be a macro that updates Column A in the way I specified whenever text is entered into Column B?

Tudball

[patel45]

Now I did not understand, can you show some examples ?

[HaHoBe]

Hi, Tudball,

maybe use the Worksheet_Change-Event behind the worksheet, limit the range to Column B and use WorksheetFunction.Max to find out the highest number given up to then. Only write this information to Column A if the cell is empty (Worksheet_Change canīt distinguish between new entry or editing an existing).

Example:

Please Login or Register  to view this content.

Ciao,
Holger