For Loop delete row, loop is skipping row

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, Oct 22 '18 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, Oct 22 '18 at 18:13

score 1 · Answer 1 · answered Oct 22 '18 at 18:17

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

For Loop delete row, loop is skipping row

1 Answers1