Excel Column delete using VBscript

Question

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

Thanks in advance.

Names for Named Ranges cannot contain spaces, look at your worksheet and make sure those names are correct. Might be an underscore or no space at all? — NickSlash, Dec 07 '12 at 13:12
Yes I checked,but they are "Task1 Start Date","Task1 Assigned User","Task2 Name". There are 200 columns like this in sequence. And I need to delete the column between them. But I got the error as "variable is undefined:'Range'". — Arup Rakshit, Dec 07 '12 at 13:25
@user1878162 - the error message indicates that there is no Range() function in VBScript (taking about variables is just a smoke screen). So try to prepend objSheet6. before your plain Ranges()s. — Ekkehard.Horner, Dec 07 '12 at 14:29
I have changed my code as below- For TaskCounter = 1 to 35 TaskRangeFrom="Task"&TaskCounter&" Start Date" TaskRangeTo="Task"&(TaskCounter+1)&" Name" objSheet6.Range(objSheet6.Range(TaskRangeFrom).Offset(,1),objSheet6.Range(TaskRangeTo).Offset(,-1)).EntireColumn.Delete Next But still getting some error Like "Unknown runtime error" on thins line @Ekkehard.Horner — Arup Rakshit, Dec 07 '12 at 14:38
@jagsler can you help me here please? its too much painful for me. My code got stuck at this point. — Arup Rakshit, Dec 07 '12 at 14:57
Any chance you could upload a sample version of the worksheet your working from? — NickSlash, Dec 07 '12 at 15:45
you cannot upload directly here, you would need to use some other service and provide a link. dropbox, filedropper etc — NickSlash, Dec 07 '12 at 15:52

bonCodigo · Answer 1 · 2012-12-08T09:04:23.640

0

Having seeing some complications in VBScript compared to VBA I would suggest the following rather-lazy method.

The easiest way to do anything VBScript-related in most office apps, Excel being one of them, is to start recording a macro, do what you wish manually, and then read the VBA that is generated and convert that VBA to VBScript.

Anyway here's some code to help you. Delete column E.

Const xltoLeft = -4131
StrName as string
StrName = "myfield"
Set NewWorkBook = objExcel.workbooks.add()
With objExcel
  .Sheets("Sheet1").Select '-- select is a very bad practice, I'll update it later
  '-- run your for loop
       'for i= blah blah
            If range.offset(0,i) = StrName then
                 Range.offset(0,i).Entirecolumn.delete xltoLeft
                 Msgbox "magical deletion"
                 Exit for
            End if
       'next i
 End With

Reference : DELETE EXCEL COLUMN IN VBSCRIPT

edited Dec 08 '12 at 09:04

answered Dec 07 '12 at 13:12

bonCodigo

14,268
1
48
91

Hi,Thanks for your response.But I need to delete a single between two adjacent column, as per my FOR Loops above. So how to do that? Please help me.I can't give the range,but can only give the Column number which is to be deleted. – Arup Rakshit Dec 07 '12 at 13:20
@Ekkehard.Horner Can you help me here? – Arup Rakshit Dec 07 '12 at 14:14
Have tried any code on your own to locate a column number based on its field name you have given it? Clue: R1C1 address can help you out. Or you loop through your headers/fields from start roll you find specific field and get its column then delete. Please try out. – bonCodigo Dec 08 '12 at 08:45

score 0 · Answer 2 · answered Dec 07 '12 at 15:18

0

To delete an entire column in VBScript simply do the following; This will delete the entire column A

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
    ("C:\myworkbook.xlsx")

objExcel.Visible = True
objWorkbook.Worksheets("Sheet1").Range("A:A").Delete

In VBA (macro's) just call the code below. This will delete the A column on the active sheet.

Range("A:A").Delete

answered Dec 07 '12 at 15:18

Jap Mul

17,398
5
55
66

in my excel has 600 columns,out of which i need to delete 36 columns selectives for which i wrote For loop.Thus I can give only column numbers,not the alphabetic values any i think. So Can you give me any suggestions how to do the same using column names or column numbers? – Arup Rakshit Dec 07 '12 at 15:22
How do you determine what rows are going to be deleted? – Jap Mul Dec 07 '12 at 15:35
Not Rows,, the whole column i want to delete which met the condition,as my FOR loop formed. – Arup Rakshit Dec 07 '12 at 15:37
1

Oops, meant columns ofcourse. Did you create `Named ranges` in the excel sheet? Because I think that's the only way to get a range by name.. – Jap Mul Dec 07 '12 at 15:48
Yes all the columns have been assigned a unique names.They are as follows: Business Process ID,Business Process Name,GSL NO,Supplier Name,Workflow Status,Workflow Requestor,Workflow Start Date, Workflow End Date,Process Status,Hierarchy,Parent Business Process ID,Task1 Name,Task1 Start Date,Task1 Assigned User, Task2 Name,Task2 Start Date,Task2 Assigned User,Task3 Name,Task3 Start Date,Task3 Assigned User,Task4 Name, Task4 Start Date,Task4 Assigned User..so on upto 36 tasks – Arup Rakshit Dec 07 '12 at 15:59
@NickSlash here i have pasted all the columns,out of which i want to delete only the Task1 Assigned User,Task2 Assigned User.. so till Task36 Assigned User... Hope this info will help you guys. – Arup Rakshit Dec 07 '12 at 16:03
I only wanted a copy to see if you were using named ranges, as I am unable to create them with spaces. Can you open the "Name Manager" using Ctrl+F3 (possibly version dependant) and verify that all your names appear in the window that opens. – NickSlash Dec 07 '12 at 16:09
@jagsler any idea,how can i achieve? – Arup Rakshit Dec 08 '12 at 11:47

score 0 · Accepted Answer · answered Dec 08 '12 at 18:33

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

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

Pics to give evidence:

Before

After

,Horner I am using Do While ... Loop, but when i am using Exit Loop command,,, then getting error. any idea why so? — Arup Rakshit, Dec 09 '12 at 06:49
can you look into the below post of mine? http://stackoverflow.com/questions/13788755/string-search-with-in-the-excel-column-values-row-wise — Arup Rakshit, Dec 09 '12 at 15:59

Excel Column delete using VBscript

3 Answers3

Linked