How to efficiently remove empty Excel rows using VBS?

Question

I need to remove the whole row IF column A value is empty. I have been doing it in excel and this method works best for me

.Columns("a:a").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

I need to do the same thing using vbs however, I have issues converting it to vbs

How can I convert the above line into vbs?

I looked up the xlCellTypeBlanks = 4 using F2 . But how to use the SpecialCells method ?

brettdj · Accepted Answer · 2015-03-08 02:48:32Z

1

Something like this

Const xlCellTypeBlanks = 4

Dim xlApp
Dim xlwb

Set xlApp = CreateObject("Excel.Application")
Set xlwb = xlApp.workbooks.Open("C:\temp\test.xlsm")
On Error Resume Next
xlwb.Sheets(1).Columns("a:a").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

answered Mar 8, 2015 at 2:48

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeanno · Accepted Answer · 2015-03-06 15:19:35Z

0

You will need to have an object for the Excel App, Workbook, Worksheet etc.. So something like

Dim xlApp as Object
Set xlApp = CreateObject("Excel.Application")
Dim wb as object
Set wb = xlApp.Open("YourWorbookName.xlsx")
wb.Worksheets("NameOfWorksheet").Columns("a:a").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

answered Mar 6, 2015 at 15:19

Jeanno

2,8894 gold badges26 silver badges33 bronze badges

2 Comments

Alex Over a year ago

Of course I do have an object for all of the above. It is just this line SpecialCells(xlCellTypeBlanks). that does not compile in vbs

omegastripes Over a year ago

Add Const xlCellTypeBlanks = 4

Collectives™ on Stack Overflow

How to efficiently remove empty Excel rows using VBS?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related