How to hide lines in excel programatically

[kcvinu]

Hi all,

I am trying to isert some data into an excel file. I don't know how to hide the line in excel for specific cells or range. In other words, how to show lines or borders for specific range of cells and hide lines for the rest of the cells.

If my clarification is not enough to understand, please see the attached image. I want my sheet exactly like the one in that image.

Some extra info.

This is the scenario i have used for filling data into my sheet.

1. first create an array of column headings and paste them into excel. So now my excel sheet has column headings in 13 columns from "B" to "N"

2. Create another array inside a loop and fill each column data in the same row. This process loops about 2000 times to fill the data.

Edited March 12, 2016 by kcvinu

[l3ill]

I don't think cell borders are covered in the UDF if that's what you're looking for.

https://www.autoitscript.com/wiki/Excel_UDF

under:

Features not covered by the UDF

[kcvinu]

@l3ill ,

I know that subject is not covered by the UDF. I can show the border like this

With $Sheet.Cells(2, $Cols)
            .Value = $aExcelColumns[$Cols - 2]
            .Font.Size = 14
            .Font.Bold = True
            .Interior.ColorIndex = 4 ; - green - back color
            .ColumnWidth = $aColWidth[$Cols - 2]
            .VerticalAlignment = $xlVAlignCenter
            .HorizontalAlignment = $xlHAlignCenter
            .Borders.Weight = $xlThick  ; border line thickness
            .Borders.ColorIndex = 1 ; - black  - border color

EndWith

 

[water]

I'm not sure I fully understand. Is the problem solved now?
If not what do you expect the script to do?

[kcvinu]

No @water , The  problem is not solved. Do you see the image in my first post ? Look at the whole "A" column. It didn't have any row lines. And see the second image. From "A1" it didn't have column lines. I want to make my work sheet like this.  Now i can paste the data to excel but i don't know how to hide these column and rwo lines.

[water]

You need to select the entire row/column as described here and set the borders for this range.

[kcvinu]

Thanks @water,

Let me try

 

[junkew]

 

rows(n).hidden=true    ; where n is an valid row number

to hide multiple rows

 range(rows(n1),rows(n2)).hidden=true ; will hide rows n1 though n2

[kcvinu]

Thanks @junkew,

But my program only knows the cells which it is filling data. I want to hide lines in the empty cells which my program doesn't know. And you see the image ? if i hide the row, then what about the cells which contains data ?

[water]

@junkew,

as I understand it he doesn't want to hide rows but just remove the borders from all rows/columns without any data.

[kcvinu]

@water Yes. I don't want to hide all rows and column lines. I just want to hide only row and column line without any data. That you can understand from the above image. I think i can use this scenario

1. First fill the data in required cells

2. Change the back color to all unused cells to white. - so they will be lineless. 

[water]

How do you manually remove the borders from column A?
I tried but was not able to reproduce what you posted in #1 with Excel 2010.

Edited March 13, 2016 by water

[kcvinu]

@water ,

The excel file in that image is not created by me. My freind gave me that file. He said he needs to make an excel file like this. And he gave me some sample data in a text file. All i need to do is, read those text from text file and paste it in excel file. But this line vanishing is quite difficult to me. 

[water]

Can you ask your friend how he does this "line vanishing thing"?

[water]

If the Excel file you got from your friend already contains the invisible lines, could you please post a stripped down version of this file?

[kcvinu]

Sure @water. He said that his co-worker did this as he knows vba. Due to some personal issues, he can't ask him the secret of this stuff.  And sorry, i am not allowed to give the copy of the file. But he provided an excel macro file to me which contains the code to create the excel file. But unfortunately, it is protected by a password. So i can't read the vba code.

[water]

Seems you are out of luck then
You and I do not know how to remove the lines. The guy who knows does not tell you.
As it is a co-worker you could ask your boss to force the guy to pass the password for the macro.

[kcvinu]

Yeah @water . It seems unlucky. Ok, let me try what you have said. Besides that, i need to search google for this. Thanks

 

[water]

Maybe this helps: https://support.office.com/en-us/article/Show-or-hide-gridlines-on-a-worksheet-512bb922-3942-46d0-8eb6-070fd3165e1d

[water]

It's easy

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oBook =  _Excel_BookOpen($OExcel, "C:\temp\test.xlsx")
$oExcel.ActiveWindow.DisplayGridlines = False