Columns Select selects unexpected worksheet columns

Question

I recorded a macro, which included selecting column(s) and setting the width. This worked until I made some changes in the preceding code, by editing in named ranges instead of the literal ranges recorded by the macro.

Now, the lines that select columns do not seem to be operating as expected. "Columns("A:A").Select" selects columns A:P!

It seems obvious to me that I introduced an error, but I'm not seeing it.

Sub DataImport()

    Range("A10").Select

    With ThisWorkbook.ActiveSheet.Range("Z1")
        .Formula = "=VLookup(C5, FileNameDictionary, 3, False)"
        .Value = .Value
        End With

    file_name = Range("Z1").Value
    Range("z1").Value = ""

    cx_name = "TEXT;" & Range("Cover!$C$18").Value & file_name

    With ActiveSheet.QueryTables.Add(Connection:=cx_name, Destination:=Range("ResultGrid"))
        .Name = ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    Columns("B:R").Select
    Selection.ColumnWidth = 6
    Columns("A:A").Select
    Selection.ColumnWidth = 10
    Range("A10").Select

    HideEmptyRows

End Sub

In general, you want to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Jun 28 '19 at 17:12
@cybernetic.nomad Thank you for the edit, as well as the link. I see that using the macro recorder as a teacher has its downside ;) But, I'm not sure that addresses the question? — Puddles, Jun 28 '19 at 17:19
I'd guess you have merged cells, which is another reason for avoiding selecting in code. — Rory, Jun 28 '19 at 17:47
@Rory I think you're onto something. I did merge cells in those columns in between the time it worked, and the time it didn't. — Puddles, Jun 28 '19 at 21:01

score 0 · Accepted Answer · answered Jun 28 '19 at 17:34

0

This appears to have solved the issue:

Dim ws As Worksheet
Set ws = ActiveSheet
With ws.Columns("B:R")
    .ColumnWidth = 6
    End With

With ws.Columns("A:A")
    .ColumnWidth = 10
    End With

answered Jun 28 '19 at 17:34

Puddles

122
7

FYI, you should mark your answer as "Correct" so others will know your question is answered – cybernetic.nomad Jun 28 '19 at 17:43
@cybernetic.nomad Thanks again. It says I may do so in 2 days. I wish I could mark that YOU had answered it. – Puddles Jun 28 '19 at 21:00

Columns Select selects unexpected worksheet columns

1 Answers1