2

I have two worksheets in Excel. One, ("List") has a list of 2000 or so rows. The other ("Library") has a large array of 2000 or so columns, each with 150 rows. I want to delete each column in "Library" that has nothing in it appear in "List".

What I have so far (using the CountIf function) is:

Sub DeleteLibrary()

Dim Rng As Range
Dim Keyword As Range
Dim Library As Worksheet
Dim List As Worksheet
Dim Chunk As Range
Dim X As Long

X = 1
Application.ScreenUpdating = True

Set Library = Sheets("Library")
Set List = Sheets("List")
Set Keyword = List.Range("A1:A2000")
Set Chunk = Library.Range("A1:A150").Offset(0, X)


   Do While X <= 1500
   If Application.WorksheetFunction.CountIf(Keyword, Chunk) >= 1 Then
    X = X + 1
    ElseIf Application.WorksheetFunction.CountIf(Keyword, Chunk) = 0 Then
    Chunk.Delete Shift:=xlLeft
    Else: Stop
    End If

    Loop
    End
End Sub

However, this gives me a type mismatch for the first CountIf line. Am I taking the right approach? Would Union or Intersect be a better route?

Thanks

user1996971
  • 543
  • 5
  • 20
  • 1
    Using Union or Intersect won't help - both of those methods look at which cells are in the ranges to be joined and don't look at the actual cell values. The second argument to CountIf can only be a single cell (although this doesn't seem to be explicitly documented anywhere) - the VBA version throws a Type Mismatch error if there is more than one cell in that argument and using CountIf in a worksheet formula just counts how often the value of the first cell appears – barrowc Feb 08 '16 at 14:33
  • 1
    Possible duplicate of [How to find if an array contains a string](http://stackoverflow.com/questions/11109832/how-to-find-if-an-array-contains-a-string) – Raystafarian Feb 09 '16 at 12:16

0 Answers0