Select a column by letter from activeCell (without activeCell.EntireColumn)

Question

First and foremost, the below works as expected. I'm trying to make the macro mimic one we have in word. Our word macro will select the entire column simply to display which column is currently being processed (the selection object is not used for any actual processing).

In excel, when I attempt to select the column (activecell.entirecolumn.select) if there is a merged cell it will show multiple columns. I need it only to select the letter column (pretty much the same as clicking the letter at the top) of the active cell. I'm hoping for a method that wont require me to parse the address of the cell if possible (I feel like string parsing is sloppy).

Sub setwidths()
Dim rangeName As String
Dim selectedRange As range
Dim tempRange As range
Dim x As Integer

'If only 1 cell is selected, attempt to find the correct named range
If Selection.Cells.Count = 1 Then 
    rangeName = Lib.getNamedRange(Selection) 'Built in function from my lib (works I promise)

    If rangeName <> "" Then
        Application.Goto reference:=rangeName
    End If
End If

Set selectedRange = Selection

'Go column by column asking for the width
'Made to mimic a word MACRO's behavior and moving backwards served a point in word
For x = selectedRange.Columns.Count To 1 Step -1
    Set tempRange = selectedRange.Columns(x)
    tempRange.Cells(tempRange.Cells.Count, 1).Select
'This is where the code should go to select the column
    tempRange.ColumnWidth = InputBox("This columns?")
Next
End Sub

Is there anyway to select a column by letter (range("A:A").select for instance) from within an active cell?

Edit: Record MACRO shows that columns("A:A").select is used when clicking the letter at the top; however, entering that same line into the immediate window will select all columns that merged cells are merged across same as with range("A:A").select and activecell.selectcolumn

Sub NSTableAdjust()
Dim rangeName As String
Dim selectedRange As range
Dim tempRange As range
Dim cellsColor() As Long
Dim cellsPattern() As XlPattern
Dim cellsTaS() As Long
Dim cellsPTaS() As Long
Dim result As String
Dim abort As Boolean

Dim x As Integer
Dim y As Integer

'Delete the block between these comments and run macro on 10x10 grid in excel to test
If Selection.Cells.Count = 1 Then
    rangeName = Lib.getNamedRange(Selection)

    If rangeName <> "" Then
        Application.Goto reference:=rangeName
    End If
End If
'Delete the block between these comments and run macro on 10x10 grid in excel to test

Set selectedRange = Selection
ReDim cellsArr(1 To selectedRange.Rows.Count)
ReDim cellsColor(1 To UBound(cellsArr))
ReDim cellsPattern(1 To UBound(cellsArr))
ReDim cellsTaS(1 To UBound(cellsArr))
ReDim cellsPTaS(1 To UBound(cellsArr))
abort = False

For x = selectedRange.Columns.Count To 1 Step -1
    Set tempRange = selectedRange.Columns(x)
    tempRange.Cells(tempRange.Cells.Count, 1).Select

    For y = 1 To UBound(cellsColor)
        With tempRange.Cells(y, 1).Interior
            cellsColor(y) = .Color
            cellsPattern(y) = .Pattern
            cellsTaS(y) = .TintAndShade
            cellsPTaS(y) = .PatternTintAndShade
            .Color = 14136213
        End With
    Next

    result = InputBox("This Column?")

    If IsNumeric(result) Then
        tempRange.ColumnWidth = result
    Else
        abort = True
    End If

    For y = 1 To UBound(cellsColor)
        With tempRange.Cells(y, 1).Interior
            .Color = cellsColor(y)
            .Pattern = cellsPattern(y)
            .TintAndShade = cellsTaS(y)
            .PatternTintAndShade = cellsPTaS(y)
        End With
    Next

    If abort Then
        Exit Sub
    End If
Next
End Sub

My current solution to simply shade the cells and then restore their original shading after processing the column.

@GeneSkuratovsky Did I spend 5 hours going over every post I could find? No. Did I do several searches with varying keywords and click all of the suggested topics that appear while filling out my question? yes. Could you post a link to one of the many posts you mention instead of being rude? Evidently not. — gNerb, Dec 28 '14 at 15:16
Probably the only way will be to remove the merged cells. With them it seems that when column is selected programatically than all other columns which are part of the merged cells(s) are selected as well. — Daniel Dušek, Dec 28 '14 at 15:45
@dee I wish that were an option :( As of now, I go through the column collecting the interior properties of each cell. Change the color to a medium blue and then restore the interior properties of the cells. This is very much not efficient which is why I left the question open. — gNerb, Dec 28 '14 at 15:48
[How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Dmitry Pavliv, Dec 28 '14 at 15:54
@simoco, Toby wants to use `SELECT` to highlight the column, so perhaps that fine post isn't relevant in this case. — Doug Glancy, Dec 28 '14 at 16:07
@GeneSkuratovsky The methods don't work because even though they will programmatically select the column correctly, they will not display the selection to the end user correctly. I have edited the post once more to show my current solution to help show what I am aiming at (my current solution is by no means efficient or ideal). — gNerb, Dec 28 '14 at 16:13
I agree with @dee that unless you unmerge the cells, you can't get around this behavior. This is a big part of why merged cells are evil :). A workaround might be to just select an area of unmerged cells in the column. Or perhaps you could do something with conditional formatting. Or perhaps another approach, like a messagebox or information in the status bar telling the user what's being done. — Doug Glancy, Dec 28 '14 at 16:22
@DougGlancy the goal is to get ti to "look" the same that it does in word. The word macro has stood the test of time and that is what the end user wants. The formatting I have applied (example above) is the closest I've found so far; however, if the table has shading applied conditionally, the conditional shading takes priority and makes it look kinda wonky. I may have to create a new conditional rule and set it to highest priority instead; which is far less efficient then the above. — gNerb, Dec 28 '14 at 16:25
I forgot about the part that it has to match an existing tool. You're pretty well hemmed-in. Good luck! — Doug Glancy, Dec 28 '14 at 16:27
@DougGlancy which is why I asked the question :D I was hoping there was a way to achieve the same visual through the selection object that you get when you click a letter on the top of excel. Which it seems is just not likely :P — gNerb, Dec 28 '14 at 16:29

score 1 · Accepted Answer · answered Dec 28 '14 at 17:02

After an obviously lengthy discussion in the comments on the post. It appears the answer to my question is simply "Not Possible."

The solution I settled on in an attempt to get as close to the "Look" I was searching for is below:

For x = selectedRange.Columns.Count To 1 Step -1
    Set tempRange = selectedRange.Columns(x) 'Range of the column

    'Our standards dictate the last cell in the range will not be merged
    With tempRange.Cells(tempRange.Cells.Count, 1) 
        .Select 'Selecting here will for excel to make sure the range is in view
        'Very simple/basic conditional formatting rule
        Set fCondition = .EntireColumn.FormatConditions. _
            Add(Type:=xlExpression, Formula1:="=True")
            fCondition.Interior.Color = 15123099
        'Make sure it is the highest priority rule
        fCondition.Priority = 1
    End With

    'Get user input
    result = InputBox("This Column?")

    'Delete rule
    fCondition.Delete

    'Validate user input
    If IsNumeric(result) Then
        tempRange.ColumnWidth = result
    Else
        abort = True
    End If

    If abort Then
        Exit Sub
    End If
Next

Select a column by letter from activeCell (without activeCell.EntireColumn)

1 Answers1