0

I have a macro-enabled sheet which has three worksheets. I want to delete the entire row if the value in column F is "T" only from sheet1. Here's the code I tried:

Sub RemoveRows()
Dim i As Long
Set ws = Sheets("Sheet1")
lRow = ws.Range("F" & Rows.Count).End(xlUp).Row 
With ws
For i = 2 To lRow 
If InStr(.Range("F" & i), "T") > 0 Then
ThisWorkbook.ActiveSheet.Range("F" & i).EntireRow.Delete
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

It deleted the rows, but not completely. Still, I can see the value "T" in column F. Can anyone please point out me with the wrong in the code?

Improve this question
1
  • Its because when you delete a row, VBA doesn't know that i is being used as a row index. e.g. if i is pointing to row 5 and you delete row 4 then what was row 5 becomes row 4 and what was row 6 becomes row 5 etc. But i doesn't know this so when you get to the next statement i is incremented to 6 which means you have skipped over row 4(used to be row 5). This is a very common gotcha in VBA. You can avoid this issue by iterating the loop from the highest row number to the lowest row number which does not affect the relationship between i and the row you think it is pointing to. Commented Jan 16, 2021 at 16:06

3 Answers 3

Reset to default
3

It will miss some of the rows that are immediately below other rows that were deleted. This is why you should loop backwards like this:

Sub RemoveRows()
Dim i As Long
Set ws = Sheets("Sheet1")
lRow = ws.Range("F" & Rows.Count).End(xlUp).Row 
With ws
For i = lRow To 2 Step -1 
If InStr(.Range("F" & i), "T") > 0 Then
ws.Range("F" & i).EntireRow.Delete
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you! but I need to run the code several times to see the result in the worksheet. Is it because of any bug in the code?
I am a little confused: Have you tried my correction of the code? did you notice that For i = 2 to lRow (in your post) changed to For i = lRow To 2 Step -1 (in my answer)?
Yes, and it deleted all the rows, but it's not happening in the first run.I need to run the code 3 or 4 times.
Try changing ThisWorkbook.ActiveSheet to ws. It might not be the active sheet when you're running the code.
0

This worked for me for one sheet as is just a minor tweak on what's already out there.

Option Explicit ' Make your intentions known.
' Option Compare Binary ' Introduce case sensitivity (Optional)

Public Sub RemoveRows()

' Activate our worksheet
ThisWorkbook.Worksheets("Sheet1").Activate

Dim i as Long
Dim my_rng as Range
Set my_rng = Range("F2")

' We'll start at row 2 and go down to as many filled rows are in the column
For i = my_rng.Row to Cells(Rows.Count, my_rng.Column).End(xlUp).Row
    With Range("F" & i)
        ' vbBinaryCompare to consider "T" vs "t"
        If InStr(1, .Value, "T", vbBinaryCompare) > 0 Then
            .EntireRow.Delete
        End If
    End with
Next i

' Clean up objects
Set my_rng = Nothing

End Sub
Improve this answer

7 Comments

You haven't addressed the issue of skipping some lines. You either loop backwards or if you want to loop forward (like you did), you need to set i = i - 1 inside the If statement (not very nice in a For loop)
Another minor issue is that the way you used End(xlDown).Row assumes that the column doesn't have empty cells, which hasn't been mentioned in the post.
@SuperSymmetry What do you mean "skipping some lines?" I didn't see any line skipping requirement from OP, but I may have missed that.
Say both cells F10 and F11 have "T" and let's call row 10 row_t and row 11 row_e (just random names). When i = 10 your code will correctly delete row_t which means row_e immediately becomes row 10 (i.e. before i changes for the next iteration). When i = 11 your code is then going to check the row below row_e. Therefore, row_e is completely skipped.
@SuperSymmetry Hi. Thanks, I updated it. I also updated having "T" vs. my original "F."
|
0

It’s always tricky when you combine a loop with a delete row. In a lot of cases you can achieve the same end by the use of a filter – and deleting all the rows en masse. The first code deletes all rows on a single sheet (Sheet1) where it finds a T in column F. The second deletes rows on the first 3 sheets.

Option 1 - first sheet only

Sub RemoveRows1sheet()

With Sheet1.Cells(1, 1).CurrentRegion
    .AutoFilter 6, "*T*"                '<~~ Filter for any instance of "T" in column F (6)
    .Offset(1).EntireRow.Delete         '<~~ exclude headers & delete rows
    .AutoFilter                         '<~~ turn off AutoFilter
End With

End Sub

Option 2 - delete rows on 3 sheets based on Sheet1 values

Option Explicit
Sub RemoveRows3sheets()

Dim LastRow As Long, i As Long                  '<~~ declare variables
LastRow = Cells(Rows.Count, 6).End(xlUp).Row    '<~~ set the last row

With Sheet1.Cells(1, 1).CurrentRegion           '<~~ apply the filter to sheet 1
    .AutoFilter 6, "*T*"
End With

For i = LastRow To 2 Step -1    
        If Sheet1.Rows(i).Hidden Then
        GoTo skip
        Else
        Sheet1.Cells(i, 1).EntireRow.Delete  '<~~ delete this row number from each sheet
        Sheet2.Cells(i, 1).EntireRow.Delete
        Sheet3.Cells(i, 1).EntireRow.Delete
        End If
skip:
Next i

Sheet1.AutoFilterMode = False                              '<~~ turn off the AutoFilter

End Sub
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.