VBScript for removing rows in excel based on empty column values

Question

I'm a VBScript learner. I'm trying to delete the rows if a particular column's cell value is empty in excel. This is the snippet which I've written.

set mysheet1 = myxl.ActiveWorkbook.Worksheets(1) 
Row1=mysheet1.UsedRange.Rows.Count
For  i= 1 to Row1
  If len(mysheet1.cells(i,1).value) = 0 then
          mysheet1.Cells(i, 1).EntireRow.Delete
  End If
Next

Unfortunately it's not deleting the rows. What might be the issue? Please help. Thanks in advance.

Gary's Student · Accepted Answer · 2018-05-23 13:12:54Z

3

Go backwards

Replace:

For  i= 1 to Row1

with:

For  i= Row1 to 1 Step -1

EDIT#1:

The reason that when we delete rows, we always go from bottom to top is that if we went the other way (the natural way) the loop indexing gets messed up.

Say we are going top-to-bottom and just examined and deleted row #5; we would like to examine row #6 next, but the delete changed it to row #5. This causes us to missing examining the old row #6 entirely!.

When we go from bottom-to-top, deleting rows does not affect the loop.

edited May 23, 2018 at 13:12

answered May 20, 2018 at 21:22

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

2 Comments

arunpandiyarajhen Over a year ago

please help me to understand the about change. Thanks in advance.

Gary's Student Over a year ago

@arunpandiyarajhen See my EDIT#1

Collectives™ on Stack Overflow

VBScript for removing rows in excel based on empty column values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related