Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

Macro not woking

Not very familiar with excel 2000
First time i used this macro it worked on another worksheet, but not this one

Public Sub RemoveRows()
Dim SearchString As String
Dim i As Long
SearchString = "Authorization"
i = 1

Do While i <= ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Rows.Count
Debug.Print ThisWorkbook.ActiveSheet.Cells(i, 3).Text
    If InStr(1, ThisWorkbook.ActiveSheet.Cells(i, 3).Text, SearchString, vbTextCompare) > 0 Then
        ThisWorkbook.ActiveSheet.Cells(i, 1).EntireRow.Delete
    Else
        i = i + 1
    End If
Loop
Excel.jpgDebug giving these results
33
39
free
44
35
trying to remove row if 3rd column contains the string "Authorization"
what is the problem ?
0
isnoend2001
Asked:
isnoend2001
  • 2
2 Solutions
 
Martin LissOlder than dirtCommented:
Anytime you delete rows from a range you need to loop backward through the range, so

For ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Rows.Count to 1 Step -1
Debug.Print ThisWorkbook.ActiveSheet.Cells(i, 3).Text
    If InStr(1, ThisWorkbook.ActiveSheet.Cells(i, 3).Text, SearchString, vbTextCompare) > 0 Then
        ThisWorkbook.ActiveSheet.Cells(i, 1).EntireRow.Delete
    Else
        i = i + 1
    End If
Next

Open in new window


If that doesn't work then please attach your workbook.
0
 
Michael FowlerSolutions ConsultantCommented:
Looping backward through the range is the right technique but here is a working example

Public Sub RemoveRows()
    Dim SearchString As String
    Dim i As Long
    
    SearchString = "Authorization"
    
    For i = Range("A1").CurrentRegion.Rows.Count To 1 Step -1
        Debug.Print Cells(i, 3).Text
        If InStr(1, Cells(i, 3).Text, SearchString, vbTextCompare) > 0 Then
            Cells(i, 1).EntireRow.Delete
        End If
    Next
End Sub

Open in new window

0
 
isnoend2001Author Commented:
Thanks, guys lot of flickering, but it works
0
 
Martin LissOlder than dirtCommented:
To stop the flickering put

Application.ScreenUpdating = False at the start of the code and Application.ScreenUpdating = True at the end.

In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now