kittu akula
New Member
Hi,
I am trying to correct this code .
in this code
dict.Item("Sheet1")= Array("s.no","cust.name","product","date") always deleting "s.no" columns from the sheet1 , i tried with different headings result is same.
I am trying to correct this code .
Code:
Option Explicit
Sub delColumnsNotInDictionary()
Dim d As Long, ky As Variant, dict As Object
Dim c As Long, lc As Long
Set dict = CreateObject("Scripting.Dictionary")
dict.comparemode = vbTextCompare
dict.Item("Sheet1") = Array("s.no", "cust.name", "product", "date")
dict.Item("Sheet2") = Array("prod.disc", "address", "pin")
dict.Item("Sheet50") = Array("foo", "bar")
With ThisWorkbook
For Each ky In dict.keys
With Worksheets(ky)
lc = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False).Column
For c = lc To 1 Step -1
'filter array method of 'not found in array'
'WARNING! CASE SENSITIVE SEARCH - foo <> FOO
If UBound(Filter(dict.Item(ky), .Cells(1, c).Value2)) = -1 Then
'.Cells(1, c).EntireColumn.Delete
Else
Debug.Print .Cells(1, c).Value2 & " at " & _
UBound(Filter(dict.Item(ky), .Cells(1, c).Value2))
End If
'worksheet MATCH method of 'not found in array'
'Case insensitive search - foo == FOO
If IsError(Application.Match(.Cells(1, c).Value2, dict.Item(ky), 0)) Then
.Cells(1, c).EntireColumn.Delete
Else
Debug.Print .Cells(1, c).Value2 & " at " & _
Application.Match(.Cells(1, c).Value2, dict.Item(ky), 0)
End If
Next c
End With
Next ky
End With
dict.RemoveAll: Set dict = Nothing
End Sub
in this code
dict.Item("Sheet1")= Array("s.no","cust.name","product","date") always deleting "s.no" columns from the sheet1 , i tried with different headings result is same.