?
Solved

Macro not woking

Posted on 2015-02-01
4
Medium Priority
?
92 Views
Last Modified: 2015-02-02
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
Comment
Question by:isnoend2001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 1000 total points
ID: 40583203
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
 
LVL 23

Assisted Solution

by:Michael Fowler
Michael Fowler earned 1000 total points
ID: 40583261
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
 

Author Closing Comment

by:isnoend2001
ID: 40583389
Thanks, guys lot of flickering, but it works
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40584246
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question