Unhide all Hidden Excel Columns Without Individual Iteration

0 votes

Can all hidden columns in an Excel worksheet be revealed?

This is the code I use to reveal every hidden row. I don't want to repeatedly cycle through and verify how many columns are being used in the range.

foreach (string filePath in _allFiles)
        {
            try
            {
                _wb = _app.Workbooks.Open(filePath, ReadOnly: false);

                foreach (Excel.Worksheet ws in _wb.Worksheets)
                {
                    try
                    {
                        string abc = ws.Name;
                        ws.ShowAllData(); /* needs try catch 'cause if no filters are applied, it will throw an exception */
                    }
                    catch (Exception ex)
                    {   }
                }
            }
            catch (Exception ex2)
            {   }
            finally
            {
                _wb.Save();
                _wb.Close();
                //_app.Quit();
                //Marshal.ReleaseComObject(_app);
            }
        }
        _app.Quit();
        Marshal.ReleaseComObject(_app);
Dec 16, 2022 in Others by Kithuzzz
• 38,020 points
919 views

1 answer to this question.

0 votes

I discovered that the best way to figure out how to perform a given activity is to develop a VBA macro of the procedure. The macro should then be converted into interop calls.

What I've discovered is that the columns and rows collection can be called for each workbook and set (as in the VBA macro) as follows:

Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False

You still have to iterate between the workbooks and call the appropriate C# interop methods.

answered Dec 16, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,720 points
1,504 views
0 votes
1 answer

Excel VBA Protect Sheet without Locking all cells

If the problem is the use of Clear then ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,720 points
914 views
0 votes
1 answer

Transform Excel table columns (not all) into rows - long to wide transformation

In PQ, highlight the Group column and ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,720 points
366 views
0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,720 points
685 views
0 votes
1 answer

How to reference Microsoft.Office.Interop.Excel dll?

Use NuGet (VS 2013+): The NuGet package manager ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,720 points
2,781 views
0 votes
1 answer

How to open an Excel file in C#?

You must have Microsoft Visual Studio Tools ...READ MORE

answered Nov 13, 2022 in Others by narikkadan
• 63,720 points
1,219 views
0 votes
1 answer

How do I get an Excel range using row and column numbers in VSTO / C#?

Use: int countRows = xlWorkSheetData.UsedRange.Rows.Count; int countColumns = xlWorkSheetData.UsedRange.Columns.Count; object[,] ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,720 points
1,658 views
0 votes
1 answer

Generate a flat list of all excel cell formulas

Hello, you'll have to follow certain steps ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
513 views
0 votes
1 answer

Is there a function to unhide columns in excel through python

Excel file : df.to_excel('demofile.xlsx',index=False) import openpyxl py = openpyxl.load_workbook('demofile.xlsx') exlsheet = ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,720 points
2,150 views
0 votes
1 answer

How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,720 points
1,608 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP