0

I've tried many things already but none of them are working.

I'm trying to loop through the 2nd cell (1st after header) of each column, check what they are and delete them if they fulfill certain conditions. The idea is to loop up to the end of the document (last column) and only there stop and display a message box.

Thing is, every time it encounters a cell without one of the specified values (the ones to delete) it breaks the loop and displays the Message Box

How could I fix this?

 Sub DeleteColumns()
 
 Set Data = Range("A1:AT1")
 
 For Each cell In Data
 If cell.Value = "Product Id" Or cell.Value = "Status" Or cell.Value = "Constdate" Or cell.Value = "Warranty Months" _
 Or cell.Value = "Partner" Or cell.Value = "Warranty Id" Or cell.Value = "Warranty Type" Or cell.Value = "Date Check" _
 Or cell.Value = "Order Number" Or cell.Value = "Delivery Number" Or cell.Value = "Return Date" Or cell.Value = "Pop Date" _
 Or cell.Value = "User text" Or cell.Value = "Error Message" Or cell.Value = "QuickSheetDesc" Or cell.Value = "PTC" _
 Or cell.Value = "Prod_Enum" Or cell.Value = "Wty_Product" Or cell.Value = "BP1 Discount" Then
 cell.EntireColumn.Delete
 Else
 
 End If
 
 Next cell
 
 MsgBox "Columnas eliminadas"
 
 
 End Sub

I tried with GoTo functions, with ElseIf Not IsEmpty(cell) and a few other things that didn't work, so I guess it's bad syntax/logic from the get go.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Gonzalo LLera
  • 1
  • Are you sure that every text equals to the collection you provided? – Maciej Los Feb 28 '23 at 21:08
  • 3
    What mean by "2nd cell" ? `Range("A1:AT1")` Is row 1 – CDP1802 Feb 28 '23 at 21:17
  • Make a range and delete it at the end, deleting columns makes the cells move around (i.e. if you delete column C then the C1 becomes B1, but the macro goes on to the new C1, skipping the old C1) That or loop backwards – cybernetic.nomad Feb 28 '23 at 22:07

0 Answers0