Solved

HOW FIX ERROR 424 VBA

Posted on 2008-10-08
2
1,110 Views
Last Modified: 2013-11-25
Hello

i got the error 424 en VBA/excel ,  and i couldn't understand the error or fix.

The error stop in ..... Is Nothing Then DelRange.EntireRow.Delete

Thanks

Itzel


Sub KillStr()

    Dim Myrange As Range, DelRange As Range, C As Range

    Dim FirstAddress As String

    Dim MyStr, MyElm

    

    Columns("E:E").Select

    Selection.Delete Shift:=xlToLeft

    Columns("F:F").Select

    Selection.Delete Shift:=xlToLeft

    

 

    MyStr = Array("5GFI10", "5MAC20", "INT515", "5MAC15", "5MAC05", "5MAC10", "5TYC10", "5GVW10")

      

    Set Myrange = Intersect(ActiveSheet.UsedRange, Range("b2:b25000"))

    If Myrange Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

 

    For Each MyElm In MyStr

        Set C = Myrange.Find(MyElm, Myrange.Cells(1), xlValues, xlPart)

        If Not C Is Nothing Then

            Set DelRange = C

            FirstAddress = C.Address

            Do

                Set C = Myrange.FindNext(C)

                Set DelRange = Union(DelRange, C)

            Loop While FirstAddress <> C.Address

        End If

        If Not DelRange Is Nothing Then DelRange.EntireRow.Delete

    Next MyElm

Open in new window

0
Comment
Question by:Itzel05
2 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 300 total points
ID: 22669425
This looks like one of mine thta has bene adapted. Try this below - it avoids deleting the range until the end which stops the code short cicuiting if the look at range is deleted mid code

Cheers

Dave
Sub KillStr()

    Dim Myrange As Range, DelRange As Range, C As Range

    Dim FirstAddress As String

    Dim MyStr, MyElm

    

    Columns("E:E").Select

    Selection.Delete Shift:=xlToLeft

    Columns("F:F").Select

    Selection.Delete Shift:=xlToLeft

    

 

    MyStr = Array("5GFI10", "5MAC20", "INT515", "5MAC15", "5MAC05", "5MAC10", "5TYC10", "5GVW10")

      

    Set Myrange = Intersect(ActiveSheet.UsedRange, Range("b2:b25000"))

    If Myrange Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

 

    For Each MyElm In MyStr

        Set C = Myrange.Find(MyElm, Myrange.Cells(1), xlValues, xlPart)

        If Not C Is Nothing Then

            Set DelRange = C

            FirstAddress = C.Address

            Do

                Set C = Myrange.FindNext(C)

                Set DelRange = Union(DelRange, C)

            Loop While FirstAddress <> C.Address

        End If

    Next MyElm

    If Not DelRange Is Nothing Then DelRange.EntireRow.Delete

End Sub

Open in new window

0
 
LVL 59

Assisted Solution

by:Saurabh Singh Teotia
Saurabh Singh Teotia earned 200 total points
ID: 22669437
There you go...use this...

Saurabh...


Sub KillStr()

    Dim Myrange As Range, DelRange As Range, C As Range

    Dim FirstAddress As String

    Dim MyStr, MyElm

    

    Columns("E:E").Select

    Selection.Delete Shift:=xlToLeft

    Columns("F:F").Select

    Selection.Delete Shift:=xlToLeft

    

 

    MyStr = Array("5GFI10", "5MAC20", "INT515", "5MAC15", "5MAC05", "5MAC10", "5TYC10", "5GVW10")

      

    Set Myrange = Intersect(ActiveSheet.UsedRange, Range("b2:b25000"))

    If Myrange Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

 

    For Each MyElm In MyStr

        Set C = Myrange.Find(MyElm, Myrange.Cells(1), xlValues, xlPart)

        If Not C Is Nothing Then

            Set DelRange = C

            FirstAddress = C.Address

            Do

                Set C = Myrange.FindNext(C)

                Set DelRange = Union(DelRange, C)

            Loop While FirstAddress <> C.Address

        End If

        If Not DelRange Is Nothing Then

        DelRange.EntireRow.Delete

       Set DelRange = Nothing

        End If

        

    Next MyElm

End Sub

Open in new window

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now