VBA to delete rows is not deleting indents (1 Viewer)

tmyers

Well-known member
Local time
Today, 16:59
Joined
Sep 8, 2020
Messages
1,090
Today is just Excel day apparently.

I was assigned another small project to revamp a process we do to make things more user friendly so everyone can do it without much explanation. I am exporting a sheet from Smartsheets to Excel via its export function. I am then copying all of that data into another excel sheet and trying to run some code to clean it up.

I am running a small block to check cell value/text, then deleting according to it:
Code:
Dim Cell         As Range

    For Each Cell In Range("B1:B300")
        If Cell.Text = "TRUE" Then
            Cell.EntireRow.Delete
        End If
    Next Cell
The code runs and works, but only on rows that are not indented. For some reason, it skips over those. I tried changing my IF statement to If Cell.IndentLevel > 1, but that did not work either. I am really confused as to why.
 

Isaac

Lifelong Learner
Local time
Today, 13:59
Joined
Mar 14, 2017
Messages
8,738
I am not 100% sure this applies to your specific code, the way you're looping, but generally speaking, when you're looping through rows and deleting them, you have to loop backwards. The reason is, otherwise, the row index confuses the looping mechanism, because it's constantly changing. It makes some kind of vague sense if you think about it.

Thus, you have to do something more like this:

Code:
Sub test()
Dim x As Long
For x = 300 To 1 Step -1
    If Cells(x, 2).Text = "TRUE" Then
        Rows(x).Delete
    End If
Next x
End Sub

I highly recommend you don't use unqualified references to things.
Range should be qualified by a worksheet, and worksheet should be qualified by something like...ThisWorkbook.

Code:
Sub test()
Dim x As Long, ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

For x = 300 To 1 Step -1
    If ws.Cells(x, 2).Text = "TRUE" Then
        ws.Rows(x).Delete
    End If
Next x
End Sub

Does it sound like I am being picky? Probably. But best to get in that habit, because if you write code like that and deploy it far and wide, eventually you'll get users who are busy on their machines, and not 'waiting' (with hands off the keyboard) for your macro to finish. Suddenly they'll report an error like "Application- or Object-defined error" (without telling you they had 6 other excel workbooks open at the same time), and it'll be....next to impossible to troubleshoot.
 

tmyers

Well-known member
Local time
Today, 16:59
Joined
Sep 8, 2020
Messages
1,090
I am not 100% sure this applies to your specific code, the way you're looping, but generally speaking, when you're looping through rows and deleting them, you have to loop backwards. The reason is, otherwise, the row index confuses the looping mechanism, because it's constantly changing. It makes some kind of vague sense if you think about it.

Thus, you have to do something more like this:

Code:
Sub test()
Dim x As Long
For x = 300 To 1 Step -1
    If Cells(x, 2).Text = "TRUE" Then
        Rows(x).Delete
    End If
Next x
End Sub
That did it. I was wondering if going the direction I was going with my looping was causing it. I am not good at looping. Not good at all.
 

Users who are viewing this thread

Top Bottom