使用 VB 脚本删除其任何单元格中不包含数据的 Excel 列 [英] To delete an Excel column which contains no data in any of the cell of it using VB script

问题描述

如果该列不包含单个值，是否有使用 vb 脚本删除 Excel 列的更快过程?

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

更新:

能否也说一下如何统计Excel每行的数据数?例如

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

现在 VBScript 应该给我这样的计数，Row1 包含 4 个数据，Row2 包含 2 个数据，Row3 包含 2 个数据，就像 wise .

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

代码更新

我已经参考你的代码更新了我的代码.并使用Hi"作为弹出框，查看控件是否进入If Body.但弹出框从未出现.在调用Application.WorksheetFunction.CountBlank(rg)"中似乎发生了一些错误.你能在这里检查并帮助我吗?没有任何列被删除，它们应该在那里.

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.

谢谢

推荐答案

你的想法很完美，只是我在代码中做了一点改动.我们需要使用递减循环，而不是递增循环.否则，并非所有列都会被 Delete 函数删除.这里更改和更新的代码是:

Your idea was perfect,except a bit change that i made in the code. We need to use the Decrement For Loop,instead of Increment Loop. Otherwise not all the columns would be deleted by the Delete function. here the changed and updated code is:

Sub DeleteColumns(Ob6)
  Dim CountBlank 
  Dim rows 
  Dim rg,c

  For c = 155 To 150 step - 1
    Set rg = Ob6.Range(Ob6.Columns(c),Ob6.Columns(c))
    CountBlank = objExcel1.Application.WorksheetFunction.CountBlank(rg)
    rows = rg.rows.Count
         'MsgBox("CountBlank:"&CountBlank)
         'MsgBox("Count:"&rows)
    If CountBlank = (rows-1) Then ' Rows-1 means the count should start from the 2nd row onward

        rg.EntireColumn.Delete

    End If
 Next

End Sub

这篇关于使用 VB 脚本删除其任何单元格中不包含数据的 Excel 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！