1

I've been using a IF condition to find the blank cells in the spreadsheet, then delete the part of the line I don't want and move the other cells up. It was going well, but when I used this database the code won't delete some lines.

Here is the following part where it find and deletes part of the line:

Dim wb As Workbook
Dim Disciplinas As Worksheet
Set wb = ThisWorkbook
Set Disciplinas = wb.Sheets("Disciplinas")

Dim start_time As range
Dim i As Integer, nLines As Integer

nLines = Disciplinas.Cells(Rows.Count, 2).End(xlUp).Row

Set start_time = Disciplinas.Rows(1).Find(what:="HR_INICIO", LookIn:=xlValues, lookat:=xlWhole)

For i = 2 To nLines
    If Disciplinas.Cells(i, start_time.Column) = "" Then
        Disciplinas.Cells(i, 2).Resize(, 13).Delete Shift:=xlUp
    End If
Next

Here is the file: Spreadsheet (the cells I'm having trouble are E105 to E125. The other ones have already been deleted in the first time I ran the code).

Instead of If Disciplinas.Cells(i, start_time.Column) = "" Then i've tried vbNullString and isEmpty() = TRUE, where all of them did not delete the lines. I've used the formula =ISBLANK() directly in the spreadsheet and the result was true.

I have noticed if I run the code multiple times eventualy it deletes all the lines I want. I ask myself why it don't remove it all in the first run? Thanks.

jf8769
  • 15
  • 4
  • 5
    Suggestion: try running the loop backwards: `For i = nLines to 2 Step -1`. – paulsm4 May 04 '17 at 07:19
  • 3
    The explanation is: Imagine you have 10 rows (numbered from 1 to 10) and you delete row 2 then rows 3 to 10 move one up and become row 2 to 9 (row 3 becomes row 2, row 4 becomes row 3, …). So the rows change their positions. **But now `i` increments and you go one line down** but it shouldn't because the next line already became the *current* line and this is why we miss that line. If you start from the end then it doesn't matter that the rows downwards move because we walk upwards. – Pᴇʜ May 04 '17 at 07:43
  • @Peh well, after you explained it seemed quite simple. Thank you! – jf8769 May 04 '17 at 15:54
  • Sorry didn't see your question earlier, jf8769. Thank you for replying, Peh. SUGGESTION: jf8769 - please add a new post with the solution (what you did), and "Accept" it. – paulsm4 May 04 '17 at 19:51

1 Answers1

0

The solution was indeed quite simple, it was just necessary to run the loop backwards, as paulsm4 suggested.

I've had to change For i = 2 To nLines for For i = nLines to 2 Step -1.

The explanation is that when VBA deletes the line and moves it up the counter increments and go one line down, "missing" some lines, as Peh explained.

jf8769
  • 15
  • 4