For Loop delete row, loop is skipping row [duplicate]

Question

I have the following For Loop. It is supposed to delete lines with all the . 's.
It is deleting the lines, but when there are rows are like the picture, the code is skipping one of them and deleting one of them.

For row_num = 8 To max_row
page_title = Sheets("Stack").Range("C" & row_num + 1).value
        If page_title = " ....................................................................................................................................................................................................................................................................................................." Then
            Sheets("Stack").Range("C" & row_num + 1).EntireRow.delete
        End If
    Debug.Print page_title
Next row_num

Can anyone help with deleting sequential rows that contain .'s ?

If you delete one, they all move up. Then you increment and you skip the highest one that moved up. — nicomp
– nicomp, Commented Oct 22, 2018 at 18:10
@BrianMStafford but then what do I do with row num ? For max_row to row_num = 8 then Next max_row ? — excelguy
– excelguy, Commented Oct 22, 2018 at 18:13

Juan Joya · Accepted Answer · 2018-10-22 18:17:25Z

1

This is beacuse the logic of the routine. For example, if you delete row 5, now row 6 will be row 5 and so on, so row 6 will not be read. Insted of delete the row, try setting the values of the column as Empty

Sheets("Stack").Range("C" & row_num + 1).EntireRow = Empty

And you will see all lines will be read.

After you finish, you can tell excel/vba to roganize your matriz according to one column, so empty rows will be organized at the end, is that to say, will become part of the rest empty spacies of the sheet

answered Oct 22, 2018 at 18:17

Juan Joya

1185 bronze badges

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

Collectives™ on Stack Overflow

For Loop delete row, loop is skipping row [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related