Excel - Something I've not come across before

[PhiltheGill]

Hi all

I've just taken over a business that relies heavily on spreadsheets and used to rely on their son for technical support.

Son has spent an awful lot of time creating macros and vb scripts to "simplify" the data entry and reporting process which, when it works, works well but is littered with bugs essentially caused by assumptions made about data (and data types) being in particular cells. Previous owner seemed to grin and bear it and would frequently spend time on the phone to son trying to correct faults.

I don't want to rely on son and I'm gradually unpicking things - essentially going back to a manual way of keying and reporting until I completely understand the business and can then look to re-automate but I've come across something I've not hit before.

In the main Excel spreadsheet (which is in effect a giant database) above the sheet itself are a number of rows - all grey in colour - that I cannot seem to delete.

There are 5 rows in all and each one has a button on the left which cannot be deleted. Pressing the button does things in the speadsheet below like hide or unhide columns.

I want to get back to a "vanilla" spreadsheet but when I try to copy and past by selecting all data the damn rows get copied across too! I'm clearly missing something here!!

Any idea how I get rid?

Thanks in advance

Phil

[Lanark]

Create references to the data in another sheet, just press = and then click on the original sheet
so you have something like =Sheet1!C5

than copy that across/up/down until you have all the data.

Copy it, paste special > Values

edit: OTOH it sounds like you maybe have outline view turned on, you can just ungroup rows/columns under Data > Outline

[mc2fool]

There are 5 rows in all and each one has a button on the left which cannot be deleted. Pressing the button does things in the speadsheet below like hide or unhide columns.

Columns or rows? Like this https://support.microsoft.com/en-us/off ... 78c49e9aff ?

If so then the answer is to click the button to reveal the extra rows, select them and click Data -> Ungroup.

[servodude]

There are 5 rows in all and each one has a button on the left which cannot be deleted. Pressing the button does things in the speadsheet below like hide or unhide columns.

Columns or rows? Like this https://support.microsoft.com/en-us/off ... 78c49e9aff ?

If so then the answer is to click the button to reveal the extra rows, select them and click Data -> Ungroup.

Might also be worth looking at whether the cells have been "merged" (and "unmerging" them if that's the case)

[pje16]

It is also possible that it may have protection and locking using VBA (macros)
they saving it as an xlsx file (that will lose the VBA)
and see if copying works then

[PhiltheGill]

None of the above seems to work I'm afraid.

Looking at the file info it is an xlsx file but it does say that it has active content.

Below is a link to an image of the top of the screen. Thought it would be easier to show rather than try and explain. The lines (7 not 5 - I clearly can't count!!) seem to be part of the same area as the formula bar and range name box..

https://1drv.ms/u/s!ApJisLNHwyydi6gtuwB ... g?e=4fWprb

[AsleepInYorkshire]

I think they are collapsed rows (do they call them grouped rows?)

Google it

But here's an example

https://www.techwalla.com/articles/how- ... el-columns

AiY(D)

[pje16]

It has grouping applied - that should be banned in Excel (grrr)
Go to data, Group, Ungroup

[monabri]

Copy the file ( for safety)
Delete the lines in grey by data ungrouping.
Unhide all columns.



I see columns are hidden. I'm guessing that different columns are hidden or made visible by selecting the group "buttons" 1 to 7.

[PhiltheGill]

Ungrouping of either rows or columns doesn't work. PC just dings for error!

Pressing the buttons seems to hide column B and AB to AH. None of the buttons seem to unhide anything but when hidden the minus turns into a plus and the columns can be unhidden by pressing the plus.

The other hidden columns are those that I've manually hidden for the time being

[PhiltheGill]

Cracked it. Finally managed to get it to clear outlines!

Thanks all for pointing me in the right direction!

Phil