Solved

HOW FIX ERROR 424 VBA

Posted on 2008-10-08
2
1,114 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

863 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

24 Experts available now in Live!

Get 1:1 Help Now