Error 424 ruinning my day

[Bonbi456]

Hi people,

I'm currently writing some code that I want to do many things, but one of them is to loop through the cells A1 to J1 while checking the value, if it is equivalent to some set values, it deletes the entire column. Right now, the code "works" but it stops and gives me an error 424 everytime the code deletes a column. After it does, I need to restart it everytime for it to work. How can I stop this?

This is my current code:

Sub Automation()

Set Mr = Range("A1:J1")
For Each cell In Mr
If cell.Value = "#" Then cell.EntireColumn.Delete
If cell.Value = "Coupler Detached" Then cell.EntireColumn.Delete
If cell.Value = "Coupler Attached" Then cell.EntireColumn.Delete
If cell.Value = "Host Connected" Then cell.EntireColumn.Delete
If cell.Value = "End Of File" Then cell.EntireColumn.Delete
Next


End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Bonbi()
   Dim Cl As Range, Rng As Range
   
   For Each Cl In Range("A1:J1")
      Select Case Cl.Value
         Case "#", "Coupler Detached", "Coupler Attached", "Host Connected", "End Of File"
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
      End Select
   Next Cl
   If Not Rng Is Nothing Then Rng.EntireColumn.Delete
End Sub

 

[Bonbi456]

Hi & welcome to MrExcel.
How about

Sub Bonbi()
   Dim Cl As Range, Rng As Range
  
   For Each Cl In Range("A1:J1")
      Select Case Cl.Value
         Case "#", "Coupler Detached", "Coupler Attached", "Host Connected", "End Of File"
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
      End Select
   Next Cl
   If Not Rng Is Nothing Then Rng.EntireColumn.Delete
End Sub

YEA!! That works! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Bonbi456]

You're welcome & thanks for the feedback.

Quick question, if I want the code to do many things, should I try to put it all in the same "Sub" or should I do each step in a different one?

 

[Fluff]

Really depends, there is no "right" way of doing it, just use whatever you are happiest with.