Worksheetfunction CountBlank

[Valmont]

Hoi Lezer,

Was een sub aan het schrijven om lege kolommen in een selectie te verwijderen.
De code hieronder werkt niet. Heb geen idee hoe te fixen. Kan iemand mij helpen?

  Public Sub DeleteEmptyColumns(ByRef TheSheet As Worksheet, ByVal first As Long, ByVal last As Long)

    Dim i As Long
    Dim Wkshtfunc As WorksheetFunction

    For i = last To first Step -1
      If Wkshtfunc.CountBlank(Directcast(TheSheet.Columns(i), Range)) = 65536 Then
        Directcast(TheSheet.Columns(i), Range).Delete()
      End If
    Next i

  End Sub

Dit is de code die bovenstaande methode aanroept:

      Dim objWorkbookFrom As Workbook
      Dim objWorkbookTemplate As Workbook
      Dim objWorksheetFrom As Worksheet
      Dim objWorksheetTemplate As Worksheet
      Dim objRangeFrom As Range
      Dim objRangeTemplate As Range

      objWorkbookFrom = objExcel.Workbooks.Open(FromLoc)
      objWorksheetFrom = DirectCast(objWorkbookFrom.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
      objWorksheetFrom.Activate()
      Dim rng As Range
      rng = DirectCast(objWorksheetFrom.Columns("A:G"), Range)
      rng.Select()

      utl.DeleteEmptyColumns(objWorksheetFrom, 1, 7)