Hide column in excel

[Malligai]

Hi,
is there any way to hide the column in excel sheet?
ex : i have 5 columns in collection.( A,B,C,D,E) In column E, i have to hide and save. i should not delete.It may use after sometime, to check that hide column.

Is there any way to hide column & save the excel.

 

[Malligai]

Dim ws,Excel,ExcelApp As Object

Try
Excel = GetWorkbook(handle,Nothing)
ExcelApp = Excel.Application
ws = GetWorkSheet(Handle, Workbook, Worksheet)
'rRng = ws.Range(RowNumberStart,RowNumberEnd)
ws.Columns(ColumnStart & ":" & ColumnEnd).Select
ExcelApp.Selection.EntireColumn.Hidden = True
'ws.HideRow(RowNumber)


Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
ws = Nothing
End Try

am using the code. But it showing error in error in initialize oage.

 

[VJR]

Hi Malligai,

It will require taking a deeper look at your code and also it does not have the actual error you are seeing in the Initialize page. But if you would like to explore an easier option then it is as below.

- Make a duplicate copy of the 'Select' action tab of the Ms Excel VBO. Right click on the tab name and you will find a duplicate option.
- The only change needed to be made in the Code stage as compared to the Select, is adding the below line.

excel.Selection.EntireColumn.Hidden = True

Rest all remains the same since you anyways need to Select the columns first before hiding them.

Dim wb, ws As Object
Dim excel, sheet, range As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)
range.Select()
excel.Selection.EntireColumn.Hidden = True

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
    range = Nothing
End Try

- For hiding the Column E in your case pass the cell reference parameter from the Process as "E:E".
For non-consecutive multiple columns pass, "A:C, F:F"
and for consecutive multiple columns pass as "A:E".

 

[Malligai]

Hi VJR, its showing the same error : couldn't execute the code.

Here my screenshots. Please help me to get resolve, its taking more time to fix it.

 

[Malligai]

When executing the code (which you have given) it shows :
Page: BSD - Hidesheet
Stage: Code1
Type: Error
Action: Validate
Description: Compiler error at line 7: Too many arguments to 'Public ReadOnly Default Property Chars(index As Integer) As Char'.
Repairable: No

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)


As am new to this, need your help.

 

[VJR]

Hi Malligai,

The new action needs to be made in the existing Ms Excel VBO. Are you doing that?
Make a duplicate copy of the 'Select' action tab of the Ms Excel VBO. Right click on the tab name and you will find a duplicate option.

The above two lines of code you have marked in bold are exactly the same as in the code stage of the 'Select' action. So if your Select action is not giving an error on these two lines then it should not be giving for the code stage of 'Hide' too, since the only different line of code is the below.
excel.Selection.EntireColumn.Hidden = True

 

[nbargaje]

Hi Malligai,

The new action needs to be made in the existing Ms Excel VBO. Are you doing that?
Make a duplicate copy of the 'Select' action tab of the Ms Excel VBO. Right click on the tab name and you will find a duplicate option.

The above two lines of code you have marked in bold are exactly the same as in the code stage of the 'Select' action. So if your Select action is not giving an error on these two lines then it should not be giving for the code stage of 'Hide' too, since the only different line of code is the below.
excel.Selection.EntireColumn.Hidden = True

@VJR ,

for this line: excel.Selection.EntireColumn.Hidden = True

if i modify this like: excel.Selection.EntireColumn.Group = True

And i tried for grouping column but got error-Exception : Failed to select row: Unable to set the Group property of the Range class


Can you please help.

Thanks,
Nitin

 

[sameerasnd]

Hi Malligai,

It will require taking a deeper look at your code and also it does not have the actual error you are seeing in the Initialize page. But if you would like to explore an easier option then it is as below.

- Make a duplicate copy of the 'Select' action tab of the Ms Excel VBO. Right click on the tab name and you will find a duplicate option.
- The only change needed to be made in the Code stage as compared to the Select, is adding the below line.

excel.Selection.EntireColumn.Hidden = True

Rest all remains the same since you anyways need to Select the columns first before hiding them.

Dim wb, ws As Object
Dim excel, sheet, range As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)
range.Select()
excel.Selection.EntireColumn.Hidden = True

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
    range = Nothing
End Try

- For hiding the Column E in your case pass the cell reference parameter from the Process as "E:E".
For non-consecutive multiple columns pass, "A:C, F:F"
and for consecutive multiple columns pass as "A:E".

Hi, VJR,

Thanks for above given guide. I used it & it works.
but i have another problem. If i having merge cell belonging the columns I going to hide, it will Hide entire merge section instead of given column reference. But i need to hide only selected columns only. it can performed manually by selecting the column headings, so how can i over come this in code stage?