Delete Lookup table but keep valuess in cells?

[AndyGray]

Hi guys, I've got a huge spreadsheet at the moment and it is growing (looking at around 900,000 rows upon completion. It is for a huge data migration procedure. I've got some V lookup tables in this, and some concatenated values which I have used for a vlookup... I want to delete this row as it will break my migration but keep the values that this row has populated?

Is it possible to delete these cells without actually effecting my lookups? I know i can obviously copy and paste the entire sheet into a new spreadsheet and just paste the values, but I think once this sheet is done it may crash excel. Any suggestions are welcome. As this will be getting exported as a tab deliminated CSV I can always format the full sheet to remove formulas and leave values if there is a way to do this also which would solve the problem.

Thanks.

 

[Youngdand]

is there anything regular in these concatenation helper rows which you can use to filter on? even possibly blanks, were to would be data in a correct row.

If so, save a copy CSV, then reopen, filter and just delete the helper rows.

 

[AndyGray]

Theres nothing really regular in this sheet. Some of the formulas are concatenation rows, some of them are V lookups, some of them are =A1 etc.. So the full sheet needs stripping of it's formula to display only values. I know it is possible to do with copy and paste values but the sheet is very very unstable. Total count was 488,000 rows in sheet 1, numerous sheets are present. Every change bumps my processor to 100% usage for about 5 minutes.