How do I unfreeze rows and columns in excel sheet

Question

0.00/5 (No votes)

See more:

Excel-2016

Hi all

Using C# in a VS project for Windows forms, I am able to freeze the active Excel spreadsheet at row and col by using -

C#

xlWorksheet.Application.ActiveWindow.FreezePanes = false;
xlWorksheet.Application.ActiveWindow.SplitRow = Row; 
xlWorksheet.Application.ActiveWindow.SplitColumn = Col; 
xlWorksheet.Application.ActiveWindow.FreezePanes = true;

My goal is to remove the data and all formatting. In other words I would like to "reverse" the above action. The end result would be a sheet set back to its 'default' condition of formatting, column sizes, row sizes and no data.

What I have tried:

The data can be cleared by
<pre lang="C#">xlWorksheet.UsedRange.ClearContents();

Intuitively, setting Row and Col =0 should work. It does not because both seem to need to be >0.

Using

C#

xlWorksheet.Application.ActiveWindow.FreezePanes = false;

does not seem to do it either. It leaves the "panes" still at the previous setting. As does

C#

xlWorksheet.UsedRange.ClearFormats();

I have also tried Clear() in place of ClearFormats() above.

A work around is to delete the sheet and open a new one of the same name.

Clearly I am missing something. Any suggestions would be welcome

Posted 16-Nov-23 12:00pm

terry_wagstaff

Add a Solution

Comments

Dave Kreskowiak 17-Nov-23 0:28am

Why not just delete the sheet and create a new one?

3 solutions

Solution 1

Seems, you want to delete cells. So, try this:

xlWorksheet.UsedRange.Delete Shift:=xlShiftUp
'unfreeze window/panes'
 ActiveWindow.FreezePanes = False

More at:
Range.Delete method (Excel) | Microsoft Learn[^]
Window.FreezePanes property (Excel) | Microsoft Learn[^]

Posted 17-Nov-23 7:56am

Maciej Los

Updated 18-Nov-23 7:36am

v2

Comments

Andre Oosthuizen 18-Nov-23 3:41am

+5ed Short and sweet.

terry_wagstaff 18-Nov-23 5:46am

Thanks for the suggestion. Unfortunately
xlWorksheet.UsedRange.Delete(XlDeleteShiftDirection.xlShiftUp);
and my original
xlWorksheet.UsedRange.ClearContents();
only clear the data. The frozen frames still persist

Maciej Los 18-Nov-23 13:36pm

Check out updated solution.

Solution 2

Quote:
How do I unfreeze rows and columns in excel sheet

As you know how to do it with the GUI, why don't you ask Excel to tell you how to do it by programming?
Ask Excel to record a new macro of your action on GUI ?

-Record a new macrodo your stuff
-End recording
-Go to VBE
-Read code generated in VBA, and adapt to your app.

As I understand your question, I think it would be easier to remove/delete rge WorkSheet and then Add a new one.

Posted 19-Nov-23 4:25am

Patrice T

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

terry_wagstaff · Accepted Answer · 2023-11-22T03:43:00

Thank you all for your suggestions. Using Patrice's prod to record a macro, I found that my original code was causing the problem. To freeze the rows at Row and Col I was using -

xlWorksheet.Application.ActiveWindow.SplitRow = Row; 
xlWorksheet.Application.ActiveWindow.SplitColumn = Col;

This 'SplitRow/SplitColumn' was apparently achieving the desired freezing, but was not easily reversible. The recorded macro showed that all that was required was to highlight the row below and the column to the right, and, then FreezePanes. So this code worked -

Range rng = xlWorksheet.Cells[Row+1, Col+1];
rng.Select();
xlWorksheet.Application.ActiveWindow.FreezePanes = true;

Then to clear the sheet of frozen panes, formatting and data the code is simply -

xlWorksheet.Application.ActiveWindow.FreezePanes = false;
xlWorksheet.UsedRange.ClearFormats();
xlWorksheet.UsedRange.ClearContents();

Additionally, replacing the ClearContents with

xlWorksheet.UsedRange.Delete(XlDeleteShiftDirection.xlShiftToLeft);

restores the column widths as well.

Thanks again