Excel VBA Column Range selection selecting wrong columns

Question

I'm working on a macro that will run on multiple worksheets in one Workbook, switching between sheets and copying/pasting data to one master sheet.

My issue is that when I switch to another sheet using Windows(index).Activate and then try to select some columns to delete, it doesn't select the columns correctly. I'm using the code below, and the EXACT same code works just fine in the master sheet, but as soon as I switch to another sheet in the VBA code and use it, it selects all columns starting with column 1 to the end of the data.

The idea behind the code below is to start at Column 1 and search for the column called "Composite Rating". I then want to DELETE all columns starting at column 3 up to the Composite Rating column.

Can someone tell me what I'm doing wrong? Thank you very much for any help!

For counter = 1 To 40 Step 1
        Cells("1", counter).Select
        strval = ActiveCell.Value
    If strval = "Composite Rating" And counter <> 3 Then
        Range(Cells(1, 3), Cells(1, counter - 1)).EntireColumn.Select
        Selection.Delete Shift:=xlLeft
        Exit For
    End If
    Next counter

EDIT: Sorry, should have mentioned my setup. I am using Excel 2007 on Windows 7. I've tried on both xls and xlsx files, same result.

score 2 · Accepted Answer · answered Mar 10 '13 at 06:49

2

Try below code :

 Dim rng As Range
    Set rng = Range("A1:Z1").Find("Composite Rating")

    If Not rng Is Nothing Then
        If rng.Column > 3 Then
            Range("C1", rng).EntireColumn.Delete
        End If
    End If

answered Mar 10 '13 at 06:49

Awesome!! That worked, thanks so much! Actually, when I changed my own code to Delete instead of using Select and then Selection.Delete, it worked too! Have you ever seen where using EntireColumn.Select actually selects ALL columns no matter what? Your code is more efficient, using the Find function, so I'll implement that instead of looping through the columns. I'm relatively new to VBA so I should search for a list of functions :) Thanks again! – zpert Mar 10 '13 at 16:37
One quick question... Would there be anything wrong with re-using the rng variable to find other ranges in the same Sub? I didn't know how VBA worked with reference variables, pointers, scope etc. if there would be an issue with resetting it while not affecting the original range it was set to. Thanks! – zpert Mar 10 '13 at 16:41
1

@zpert You can re-use rng variable. But as you are new to vba world, I wud say declare new variable and use them to avoid any kind of confusion. – Mar 10 '13 at 16:56

score 0 · Answer 2 · answered Mar 10 '13 at 05:21

0

The index property in the Windows collection changes when selecting other windows -> it's relative!

Use the Workbooks collection instead.

answered Mar 10 '13 at 05:21

Sebastian

5,177
4
30
47

Thanks Sebastian. That's good to know for the future. I was selecting the correct window, the Select function just wasn't working properly. – zpert Mar 10 '13 at 16:38

Excel VBA Column Range selection selecting wrong columns

2 Answers2