Object Required - Nested For Loop

[audioboxer]

Hi Everyone,

Can't seem to figure this one out, been picking my brain for a few hours.. I am getting object required for the x.EntireColumn.Delete portion. My guess is it's not carrying the x variable into the next for loop? Basically I'm trying to loop through every workbook in the folder and delete all columns that don't have on of the values in my Array

Any help would be appreciated. Thanks

Sub removecols()
Dim KeepCols As Variant
KeepCols = Array("Apple", "Banana", "Orange", "Yellow", "Brown")


Path = "C:\Users\User\Desktop\test\"
Filename = Dir(Path & "*.xlsx")




Do While Filename <> ""
   
Workbooks.Open Filename:=Path & Filename
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
 
 For Each x In Range("A1:A" & lastCol)
    For Count = 1 To UBound(KeepCols)
    If InStr(1, i, KeepCols(Count), vbTextCompare) = False Then
    x.EntireColumn.Delete
    Else
   End If
    Next Count
    Next x
        
  Workbooks(Filename).Close (True)
  Filename = Dir()
  Loop
  
End Sub

 

[audioboxer]

I made a mistake in the previous code with the i variable, it should have been x. So the actual error I'm getting is at the Object Required on the "If InStr(1, x, KeepCols(Count), vbTextCompare) = False Then" line.

Corrected Code

Sub removecols()
Dim KeepCols As Variant
KeepCols = Array("Apple", "Banana", "Orange", "Yellow", "Brown")


Path = "C:\Users\User\Desktop\test"
Filename = Dir(Path & "*.xlsx")




Do While Filename <> ""
   
Workbooks.Open Filename:=Path & Filename
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
 
 For Each x In Range("A1:A" & lastCol)
    For Count = 1 To UBound(KeepCols)
    If InStr(1, x, KeepCols(Count), vbTextCompare) = False Then
    x.EntireColumn.Delete
    Else
   End If
    Next Count
    Next x
        
  Workbooks(Filename).Close (True)
  Filename = Dir()
  Loop
  
End Sub

 

[MARK858]

Is the below correct?

You are getting the last column number.
Then you are using it to give the last row number.
Then if the instr is true to delete column A (which it will always be)

[COLOR="#006400"]lastCol = Cells(1, Columns.Count).End(xlToLeft).Column[/COLOR]
 
 For Each [COLOR="#006400"]x[/COLOR] In Range("A1:A" & [COLOR="#006400"]lastCol[/COLOR])
    For Count = 1 To UBound(KeepCols)
    If InStr(1, x, KeepCols(Count), vbTextCompare) = False Then
   [COLOR="#006400"] x.EntireColumn[/COLOR].Delete

 

[audioboxer]

Is the below correct?

You are getting the last column number.
Then you are using it to give the last row number.
Then if the instr is true to delete column A (which it will always be)

[COLOR=#006400]lastCol = Cells(1, Columns.Count).End(xlToLeft).Column[/COLOR]
 
 For Each [COLOR=#006400]x[/COLOR] In Range("A1:A" & [COLOR=#006400]lastCol[/COLOR])
    For Count = 1 To UBound(KeepCols)
    If InStr(1, x, KeepCols(Count), vbTextCompare) = False Then
   [COLOR=#006400] x.EntireColumn[/COLOR].Delete

Thank you, I am a dope... I've corrected it to the following however I still have the same error at the same line.

Sub removecols()
Dim KeepCols As Variant
Dim x As Range
KeepCols = Array("Manufacturer", "Manufacturer Part Number", "Description", "Quantity Available", "Part Status")


Path = "C:\Users\Sayre\Desktop\Hirose\test\"
Filename = Dir(Path & "*.xlsx")




Do While Filename <> ""
   
Workbooks.Open Filename:=Path & Filename
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
 
 For Each x In Range(Cells(1, 1), Cells(1, lastCol))
    For Count = 1 To UBound(KeepCols)
    If StrComp(x, KeepCols(Count), vbTextCompare) = 0 Then
    x.EntireColumn.Delete
    Else
   End If
    Next Count
    Next x
        
  Workbooks(Filename).Close (True)
  Filename = Dir()
  Loop
  
End Sub

 

[JoeMo]

Don't know if this will avert the error on that line, but it may help get you on the right track. Your OP indicates you want to delete all columns that don't match one of the values in your array, but that's not what your code is doing. This part:

For Each x In Range(Cells(1, 1), Cells(1, lastCol))
    For Count = 1 To UBound(KeepCols)
         If StrComp(x, KeepCols(Count), vbTextCompare) = 0 Then
            x.EntireColumn.Delete
        Else
        End If
    Next Count
Next x

is missing the first element in the array KeepCols which is a zero-based array (unless you forgot to tell us you have Option Base 1 set). And, this line:

If StrComp(x, KeepCols(Count), vbTextCompare) = 0

is true when x and keepcols(count) are equal, so you don't want to delete the entire column x is in.