vbscript - To delete an Excel column which contains no data in any of the cell of it using VB script

Question

Is there any faster process using vb script to delete an Excel column if that column contains not a single value in it?

   For Task=2 To 300

   Vcounter="False"
   IntRow6=2

   Do While objSheet6.Cells(IntRow6,1).Value = ""

  If objSheet6.Cells(IntRow6,Task).Value <> "" Or objSheet6.Cells(IntRow6,Task).Value <> "None"


   Vcounter="True"
   Exit DO

  End If

 IntRow6=IntRow6+1
 Loop

  If  Vcounter <> "True" Then

   objSheet6.Cells(1,Task).EntireColumn.Delete

  End If

 Next

Update:

Could you also say how to count the number of data in each Row of an Excel? for e.g.

        Col1   Col2  Col3   Col4   Col5

  Row1   A       B            X     P
  Row2   L       M
  Row3                 T            V

Now the VBScript should give me the count that Row1 contains 4 data,Row2 contains 2 data and Row3 contains 2 data like wise .

Code Update

I have updated my code with reference to your one. And used "Hi" as an pop-up box to see if the controls entered into the If Body.But the popup never came. It seems something wrong happened in the call "Application.WorksheetFunction.CountBlank(rg)". Can you check and help me here? None of the columns have been deleted,where they should be.

Sub DeleteColumns(Ob6)
     Dim CountBlank 
     Dim rows 
     Dim rg,c
  Set objExcel1 = CreateObject("Excel.Application")

 For c = 150 To 155
    Set rg = Ob6.Range(Ob6.Columns(c),Ob6.Columns(c))
    CountBlank = objExcel1.Application.WorksheetFunction.CountBlank(rg)
    rows = rg.rows.Count

    If CountBlank = rows Then
        MsgBox("Hi")
        rg.EntireColumn.Delete
    End If
 Next
End Sub

Fix: I just fixed it.So no trouble here. just need your help for the Update part.

Thanks

Answer 1

You can use the function CountBlank, found inside Application.WorksheetFunction

Dim CountBlank As Long
CountBlank = Application.WorksheetFunction.CountBlank(Range("A:A"))

Then, you just need to compare it to the number of rows in the same range:

Dim ws As Worksheet
Dim rows As Long
Set ws = ThisWorkbook.Worksheets(1)
rows = ws.Range("A:A").Count

The entire code for deleting an empty column, from the index 1 to 300, would look like this:

Sub DeleteColumns()
    Dim CountBlank As Long
    Dim ws As Worksheet
    Dim rows As Long
    Set ws = ThisWorkbook.Worksheets(1)
    Dim rg As Range

    For c = 1 To 300
        Set rg = Range(ws.Columns(c), ws.Columns(c))
        CountBlank = Application.WorksheetFunction.CountBlank(rg)
        rows = rg.rows.Count

        If CountBlank = rows Then
            rg.EntireColumn.Delete
        End If
    Next
End Sub