CODEWITHSUNDEEP
vbaexcel
reggie86
reggie86

Reputation: 289

Select Some Columns and Delete Others VBA

I am attempting to select columns with only certain titles, deleting all the others, but I keep getting an error. It is a Method 'Range' of object '_Global' failed, and it highlights the seventh line of code (Range(Cells(1, Col)).Select). Any help or suggestions would be greatly appreciated-- thanks!

       Dim Col As Integer
        Col = 1
        Range("A1").Select
        While Selection.Value <> ""
            If Selection.Value = "A" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "B" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "D" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "E" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "F" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "H" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "J" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "M" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "N" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "P" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "R" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "S" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "T" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "V" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            ElseIf Selection.Value = "X" Then
                Col = Col + 1
                Range(Cells(1, Col)).Select
            Else: ActiveCell.EntireColumn.Delete
            End If
        Wend

Upvotes: 0

Views: 65

Answers (2)

dyslexicgruffalo
dyslexicgruffalo

Reputation: 374

Also you can easily shorten the if statement as well by including or in your question part. 

With Selection
    If .Value = "A" or "B" or...  ...Then...

    Else...

    End If
End With

Hope this helps - https://msdn.microsoft.com/en-us/library/752y8abs.aspx

Upvotes: 2

RGA
RGA

Reputation: 2607

First, the error is caused by calling Range with only one Cell argument. If you have only one Cell, you must append the Cell with its Address property, so the line should be Range(Cells(1,col).Address).Select

Secondly, from what you've described you would like your code to do, I don't think this code will actually perform how you'd like. Frankly, I can't really tell what it is you are even trying to do from the code itself, so be forewarned that my answer will fix the error call, but it seems likely that it won't run as you are anticipating once it does run.

Upvotes: 2

Related Questions