How to hide lines in excel programatically

[kcvinu]

@water you are amazingly fast. Thanks. I am just reading the display gridlines in vba help file

 

[water]

When I found the support article and knew they were talking about gridlines and how to set/unset them it was easy to run the macro recorder and see what happens when unselecting gridlines. Then a few modifications to translate it from VBA to AutoIt. Voilá!

[kcvinu]

Thanks. And i need to hide this gridlines only for unused cells.

[junkew]

turn on macro excel recorder when you do it manually and post the recorded VBA code. After that its easy to translate to AutoIT (alhough I never understand why to do it in AutoIT when VBA can do it much quicker and easier with VBA IDE, Intellisense, Speed etc.)

[water]

Gridlines are a global setting for a worksheet. Set the grid lines, then add your data and then set the borders for the usedrange object. 

[kcvinu]

@junkew ,

My first preference is creating individual exes. I don't think vba will do this. i mean exe making. I love to make independent programs. 

 

[junkew]

I had to read your thread triple times and finally understood what you want.

a. hide the gridlines

b. make range ws.cells white borderlines

c. then import your data and color only the data you add with a range("b2:n2400") (you have to do the number last line)

 

 

[kcvinu]

@junkew ,

I am sorry if i made you to read this triple times. And i am not sure that you have understood my idea completely.  Anyhow, i got your last point. i.e (c)

[water]

I think a) plus c) should yield the desired result.

[junkew]

Fun topic.

Made an example in VBA

Algorithm I would propose to you when you write it in AutoIT (Nice in the future another coworker will not ask for a password but for a decompiler on the exe you distribute)

a. Create basic csv file from AutoIT with right headers

b. Just remotely control xlApp to read the file

c. All you then have to do is apply formatting and calculation of lastrow and lastcolumn (including alpha column character)

expandcollapse popup

Option Explicit
Dim strFileName As String 'Filename with basic salesdata with or without header
Dim xlApp As Application
Dim xlWbSales As Workbook 'Workbook with the results
Dim ws As Worksheet 'short reference to first worksheet of salesworkbook

Dim tData 'Just a variant for easy title / splitting array directly to range of cells

Dim lastRow As Long
Dim lastCol As Long
Dim lastColAlpha As String 'Column character

Dim strRange As String
Dim strFormula As String

Dim tCell As Range 'short naming references for easy reading
Dim tRange As Range 'short naming references for easy reading

'CSV sample layout
'Site;Supplier name;HPL;Item-type;Item number;Quantity
'   49500;s1             ;asdf   ;123        ;;1
'   49500;s1             ;asdf   ;123        ;;2
'   49500;s1             ;asdf   ;123        ;;3
'   49500;s1             ;asdf   ;123        ;;4
'   49500;s1             ;asdf   ;123        ;;5
'   49500;s1             ;asdf   ;123        ;;6
'   49500;s1             ;asdf   ;123        ;;7
'   49500;s1             ;asdf   ;123        ;;8
'   49500;s1             ;asdf   ;123        ;;9
'   49500;s1             ;asdf   ;123        ;;10
   
Sub MakeSalesReport()
    Set xlApp = Application                                     'For easy translation to AutoIt
    strFileName = Environ("TEMP") & "\salesdata.csv"            'I did put the CSV in my temp location
    
    
    'Import the CSV data
    Set xlWbSales = xlApp.Workbooks.Open(Filename:=strFileName, local:=True) 'Just a new workbook based on the csv read in and local makes it split csv directyl
    'Find the last row and last column
    Set ws = xlWbSales.Worksheets(1)
'Tricky this should be possible in a different way
    ws.Activate
    ActiveWindow.DisplayGridlines = False
    
    lastRow = ws.Cells(1, 1).SpecialCells(xlLastCell).Row + 1
    lastCol = ws.Cells(1, 1).SpecialCells(xlLastCell).Column
    lastColAlpha = Chr(65 + lastCol - 1) 'For easy range making assuming less then 26 columns
    
    'Do the formatting
    ws.Rows(1).Insert
    lastRow = lastRow + 1
    
    'Add the title header either with a cells or range reference
    ws.Cells(1, 1).Value = "Purchase Price Variance report as on " & Format(Now(), "dd-mmm-yyyy")
    With ws.Range("A1").Font
        .Name = "Arial Black"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    'Suppose titles are specific and not in the csvfile
    tData = Split("Site;Supplier name;HPL;Item-Type;Item Number;Quantity", ";")
            
    'Check if no header and insert it
    If UCase(Trim(ws.Cells(2, 1))) <> "SITE" Then
        ws.Rows(2).Insert
        lastRow = lastRow + 1
        ws.Range("A2:" & lastColAlpha & "2") = tData
    End If
    
    'Format the header
    strRange = "A2:" & lastColAlpha & "2"
    With ws.Range(strRange).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With ws.Range(strRange).Font
        .Name = "Arial Black"
        .Size = 14
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
     
    'Add total and sum on last row
    Set tCell = ws.Cells(lastRow, 1)
    tCell.Value = "Total"
    With tCell.Font
        .Name = "Arial Black"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
      
    'Sum cell
    Set tCell = ws.Cells(lastRow, lastCol)
    strFormula = "=SUM(" & lastColAlpha & "3:" & lastColAlpha & lastRow - 1 & ")"
    tCell.Formula = strFormula
    With tCell.Font
        .Name = "Arial Black"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
        
    tCell.Style = "Comma"
    tCell.NumberFormat = "_ * #,##0_ ;_ * -#,##0_ ;_ * ""-""??_ ;_ @_ "
   
            
    'Do all the formatting stuff
    setStyle tCell, xlEdgeLeft
    setStyle tCell, xlEdgeTop
    setStyle tCell, xlEdgeBottom
    setStyle tCell, xlEdgeRight
    setStyle tCell, xlInsideVertical
    setStyle tCell, xlInsideHorizontal

    strRange = "A2:" & lastColAlpha & lastRow - 1
    Set tRange = ws.Range(strRange)
    
    setStyle tRange, xlEdgeLeft
    setStyle tRange, xlEdgeTop
    setStyle tRange, xlEdgeBottom
    setStyle tRange, xlEdgeRight
    setStyle tRange, xlInsideVertical
    setStyle tRange, xlInsideHorizontal
   
    'Add an empty row and empty column
    ws.Columns(1).Insert
    ws.Rows(1).Insert
    ws.Columns.AutoFit
    ws.Columns(2).EntireColumn.ColumnWidth = 12
End Sub
Sub setStyle(r As Range, c As Long)
    With r.Borders(c)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub