使用VBscript删除Excel列 [英] Excel Column delete using VBscript

问题描述

我在vbscript中编写了如下代码，但是当我运行我的脚本时，出现错误，提示范围"未定义.有什么可以说出什么错误来帮助我的吗?

I wrote a code in vbscript as below ,but when i run my script it is giving an error saying the "Range" is undefined. Can any help me here by saying what is the error?

For TaskCounter = 1 to 35    
  TaskRangeFrom="Task"&TaskCounter&" Start Date"    
  TaskRangeTo="Task"&(TaskCounter+1)&" Name"    
  objSheet6.Range(Range(TaskRangeFrom).Offset(,1), _ 
  Range(TaskRangeTo).Offset(,-1)).EntireColumn.Delete
Next 

谢谢.

推荐答案

就像昨天提到的@NickSlash一样，我怀疑您是否给定了范围名称，例如 您的列的业务流程ID"(包含空格).但这可能是 一个版本的东西，我向您展示如何获取一个整个列"范围对象 列名为"TaskB"(通过定义名称"对话框):

As @NickSlash mentioned yesterday, I doubt that you have given range names like "Business Process ID" (containg spaces) to your columns. But as this may be a version thing, I show you how to get a 'whole column' range object for a column named "TaskB" (via the "define name" dialog):

' Range by Name
Set oRng = oWs.Range("TaskB")

要通过(列)数字获得第二列的范围，请使用:

To get a range for the second column by (column) number, use:

' Range by Number
Set oRng = oWs.Cells(1, 2).EntireColumn

请注意:行号和列号以1开头.因此，您的".Offset(，1)" 代码看起来很混乱；可能导致了未知的运行时错误".

Please note: row and column numbers start with 1. So your ".Offset(,1)" code looks very fishy; it may have caused the "Unknown runtime error".

如果您-我猜想-在第一行中写了列标题，您将 必须遍历该行的列并检查值:

If you - as I suppose - wrote your column titles in the first row, you'll have to loop over the columns of that row and check the values:

' Range by Lookup
Set oRng = Nothing
For nCol = 1 To 5
    If "Title B" = oWs.Cells(1, nCol).Value Then
       Set oRng = oWs.Cells(1, nCol).EntireColumn
       Exit For
    End If
Next

如果要进行实验，请将这些代码段插入测试代码中，例如:

If you want to experiment, insert those snippets into test code like:

Dim oFS    : Set oFS = CreateObject("Scripting.FileSystemObject")
Dim sDir   : sDir    = oFS.GetAbsolutePathname("..\xls")
Dim sFSpec : sFSpec  = oFS.BuildPath(sDir, "work.xls")
' Start clean
oFS.CopyFile oFS.BuildPath(sDir, "13763603.xls"), sFSpec

' Open .XLS
Dim oXls : Set oXls = CreateObject("Excel.Application")
Dim oWb  : Set oWb  = oXls.Workbooks.Open(sFSpec)
Dim oWs  : Set oWs  = oWb.Worksheets(1)
Dim oRng, nCol

' Range by XXX
...

oXls.Visible = True
WScript.Stdin.ReadLine
If Not oRng Is Nothing Then
   oRng.Delete
   WScript.Stdin.ReadLine
End If
oXls.Visible = False
oWb.Close False
oXls.Quit

提供证据的图片:

这篇关于使用VBscript删除Excel列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！