Can't hide columns in Excel with C#

Question

Hello,

i try to hide some columns in an Excel-Worksheet and "Range.EntireColumn.Hidden = true;" isn't working for me. Can someone please explain to me why?

worksheet.get_Range("A1"
, "A8"
).EntireRow.Hidden = true
;

worksheet.get_Range("E:E"
, Missing.Value).EntireColumn.Hidden = true
;

worksheet.get_Range("G:G"
, Missing.Value).EntireColumn.Hidden = true
;

worksheet.get_Range("H:H"
, Missing.Value).EntireColumn.Hidden = true
;

Hiding the rows works perfectly, but columns stay visible. I don't understand why.

Is there some sort of property, which prevents me from hidden columns?

Thx for any help 

Greetings

Answer 1

Hello,

i use EntireColumn and it works.

I see two differences:

1: i use Excel.Range eR = Globals.ThisWorkbook.eSheet_XXX.get_Range( ......

eR.Cells.EntireColumn.Hidden = false;

with eSheet_XXX is a Global Variable in ThisWorkbook

2: i don´t use "H:H", i write for example "H2"

Good luck

CD

Answer 2

Hi CD,

thx for your support. I allready tried different notations for the get_Range-methode. None of them changed the outcame.

If you know anything else, please let me know.

 

Thx

 

Yampersand

Answer 3

Hi,

perhaps you can make more information about your code.

I tried again, it worked perfect!

 

codedive

 

Answer 4

Hallo Yampersand,

Leider kann ich auch nicht Dein Problem nachvollziehen. Ich kann die Spalten einwandfrei ausblenden.

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      Excel.Application xlApp;
      Excel.Workbook xlWorkBook;
      Excel.Worksheet xlWorkSheet;

      object missing = System.Reflection.Missing.Value;

      xlApp = new Excel.ApplicationClass();
      //xlWorkBook = xlApp.Workbooks.Open(@"C:\Temp\HideColumns.xlsx",0,true,5,"","",true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,"\t",false,false,0,true,1,0);

      xlWorkBook = xlApp.Workbooks.Open(@"C:\Temp\HideColumns.xlsx",missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);

      xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
      
      //MessageBox.Show(xlWorkSheet.get_Range("B2","B2").Value2.ToString());
      
      xlWorkSheet.get_Range("B:B", missing).EntireColumn.Hidden = true;

      xlWorkBook.Close(true, missing, missing);
      xlApp.Quit();

      releaseObject(xlWorkSheet);
      releaseObject(xlWorkBook);
      releaseObject(xlApp);
    }

    private void releaseObject(object obj)
    {
      try
      {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
      }
      catch (Exception ex)
      {
        obj = null;
        MessageBox.Show("Das Objekt kann nicht freigegeben werden " + ex.ToString());
      }
      finally
      {
        GC.Collect();
      }
    }
  }
}

 

Grüße,

Robert

Answer 5

Thx to all of you for your help, i finally figured out why my columns didn't hide:

Apparently columns don't stay hidden if you make certain settings, like autofit, autofillter, columns width or horizontal alignment. So I changed the order of my statements and now everything works fine.

 

Thx again for your support.

Yampersand