Select column A:A despite merged cells in a1:c1

rberke,

You should not be using 'select' at all in VBA. The macro recorder does that but that is a poor coding as it is slow and unnecessary. You should avoid using both select and selection. You should also avoid using merged cells like the plague. They mess up everything and are really not worth the trouble they cause.

Having said that to deal with your specific issue:

Range([a2], [a65536]).Select

will work but of course it leaves out cell A1.

As they say, if all else fails, do without merged cells.

Patrick

Hi rberke,
Here is an "approximation" that works in the case where A1:C1 are merged cells, but A2:A65536 do not contain merged cells. It creates a compound range and selects that.
Union([A1].MergeArea,[A2:A65536]).Select

Since this approach doesn't generalize very well, I suggest following Patrick's advice to avoid Select, Activate and Selection whenever possible (which is almost always). Could you give us a specific problem you are trying to solve by selecting column A  and only column A?

Hoping to be helpful,

Brad

Brad:

I believe what Rberke is asking is how does he select a column without selecting more than one column when a column contains merged cells across other columns.  Personally, I don't ever use merge cells for this very reason, but that seems to be the question.

Jaes

Thanks for all your comments.  They all say "its impossible to do what you are asking", so I will split the points evenly.

I will also post a similar question in the C# forum and see if anyone there can help.

rberke

P.S. For you experts that are curious or just masochistic, I'll give one example of how I use this in real life.  

I define a range called [these] which contains a vector of cells with values like good, bad, ugly.

I call my SelectColumnsWithTheseValues() subroutine which SELECTS every column containing "good", "bad" or "ugly".

Then, depending on my mood, I MANUALLY color them as yellow, (or perhaps insert>name>define them as "importantColumns", or maybe even Edit>delete.)

So, you can see that the Select function is integral to my subroutine, and cannot be avoided.
You also see why an approximation is not a perfect alternative. Especially for the defined name option.

Also, Brad's comment that an approximation "doesn't generalize very well" was very true.  While I was able to generalize the function, it was very ugly...

Sub extendcolumns()
' first do a selfind to find cells which contain "good"
' this will create a scattered selection of individual cells.
' then call extendcolumns() so each cell's entire column will be selected.
' then edit delete if you want them to be blasted, or color them etc
Dim area As Range
Dim toSelect As Range
Dim ur As Range
Set ur = Range("a1", Range("A1").SpecialCells(xlCellTypeLastCell))
Set toSelect = Nothing

For Each area In Selection.Areas
    If toSelect Is Nothing Then
        Set toSelect = area.EntireColumn
    Else
        Set toSelect = Union(toSelect, area.EntireColumn)
    End If
Next area
toSelect.Select

If Selection.Columns.Count <> toSelect.Columns.Count Then

' ============ here is the ugly part ================
    Dim col As Range
    Dim msg As String
    msg = ""
    Dim newSelect As Range
    Dim NewCol As Range
    Dim RowIx As Long
    For Each col In toSelect.Columns
        If col.MergeCells & "" <> "True" Then msg = msg & " " & Split(col.Address, ":")(0)
    Next col
    MsgBox "these columns have merged cells which caused " & Selection.Columns.Count - toSelect.Columns.Count _
            & " extra columns to be selected. " & vbCrLf & msg

    Set newSelect = Intersect(toSelect.EntireColumn, Range(Rows(ur.Rows.Count + 1), [iv65536]))
   
    For Each cl In Intersect(toSelect.Cells, ur)
      ' MsgBox cl.Address
        If cl.MergeCells Then
            If cl.MergeArea.Address = Intersect(cl.MergeArea, toSelect).Address Then
                Set newSelect = Union(newSelect, cl) ' if the merge are is entirely contained, keep it
            End If
        Else

            Set newSelect = Union(newSelect, cl)
        End If
   
    Next cl

    newSelect.Select
    MsgBox "the mergedcells have been dropped from the selection"
   
End If

End Sub

Incidentally, I also have a SelectRowsWithTheseValues() subroutine.

This is especially useful, if my customer loves an inventory worksheet, but wants to eliminate rows with Nuts or Bolts.

If you are going to put the same question in the C# TA, please make it a pointer to this one. I'd like to see what they are able to come up with.

Brad

I posted pointers here in both C# and Vb.net.  Lets see what happens.

I'm coming in from VB.NET. I've done a limited amount of VBA programming, though I wouldn't call myself an expert. Nonetheless, a possible answer:

It's going to be rather tedious programming, I'm afraid, but I think you could resolve the problem by finding the merged cells in a section, unmerging them while remembering where the merge is, selecting the column, then restoring the merge. What's going to make it particularly tedious is there could be multiple merge groups in a single column, so you'll have to save an indeterminate number of groups.

The process would be something like this:
1) scan the column when you get a MergeCells = True for the column (which you're already looking for to print your warning message)
2) find an individual cell that has MergeCells = True
3) save its MergeArea
4) do a .MergeArea.Unmerge to unmerge the cells
5) continue scanning the column until all cells have been checked for MergeCells=True; when found, do 3 & 4
6) select the column
7) go through your list of merged areas to re-enable the merges

This is almost totally untested, though I did check to make sure that doing a Merge after selecting the column won't mess up the selection. Maybe others who are more expert in VBA for Excel will know reasons it wouldn't work. But I think it's worth a shot.

ElrondCT

That was a good idea, but it does not work:

cells.mergecells= false
[a:a].select
[a1:c1].mergecells = true

'  results in 3 columns being selected

Another good idea that does not work : Goto reference = [a:a]

Please let me clarify to c# and vb.net newcomers: I am hoping that c# or vb.net have MORE capability than vba.  I am 99.9% sure that vba itself is incapable of doing this.

Strange; I thought I checked a column .Select followed by a .Merge of three cells including one in the column, and it didn't change the selection. Now when I try to recreate that, I get the 3 columns selected. Unfortunately, I already closed the previous test spreadsheet, so I can't see exactly how I did it.

Can you do the work you want to do on the selected column before you reinstate the merge(s)?

I don't know that there's going to be anything the full programming languages can do that VBA can't. They all use hooks into the Excel engine to do their thing. However, there may be ways to "select" columns in VBA (or a language) without using the .Select method, which would allow you to do what you want. I don't know that I understand your situation enough, or am expert enough at VBA, to really take you farther on that.

ElrondCT: Thanks for trying.  

Perhaps someone else will succeed.  I will wait patiently.

LoNeRaVeR9:

That will work, but I hope to avoid it, because it is a kludge on top of a kludge.

The extendcolumns approximation that I published on 7/10/2006 08:01AM PDT is the first kludge,  It selects every cell in the column EXCEPT the merged cells.  

One annoying problem occurs when I want to clear the entire column.  Since the merged cell was not in the selection, it does not get cleared.

I believe you are suggesting I add a second kludge that does something like this at the end:

If "yes" = Inputbox("the mergedcells have been dropped from the selection. Respond 'Yes' and they will be unmerged and fully selected.") then
    rngProblemCells.MergedCells = true
     newSelect.Entirecolumn.Select
    msgbox "The problem cells have been 'unmerged', you can restore them to their original state by using ctrl shift r at a later time. "
end if


The first kludge is very safe because there are not alterations to the worksheet.  In fact, the undo change is preserved!

The second kludge is more dangerous because the undo chain is destroyed, and my program has to preserve the information about the merged cells.

I don't think the extra work is terribly difficult, so I may experiment with it. In the meantime I still hope someone will come up with the magic .singlecolumnselect method that I seek.

Please consider going back to Patrick's opening suggestion of avoiding Select and Selection.

I populated a worksheet with a number of horizontally merged ranges (each one two cells).  Underneath the merged ranges, I put the words "good" (in the left column) and "bad" (in the right column). I then used F8 to step through the following sub:

Sub SingleFile()
Dim cel As Range, rg As Range
Set rg = ActiveSheet.UsedRange
Set cel = rg.Find("good")
cel.EntireColumn.Select       'This statement selected two columns
cel.EntireColumn.Delete       'This statement deleted the column on the left
End Sub

You will note that the Select statement works in the fashion that has confounded everyone in this thread so far. But the statement that works directly with the object works exactly as intended.

Brad

This code works fine for me:

Imports Microsoft.Office
Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim eApp As New Excel.Application
        Dim eBook As Excel.Workbook
        eApp.Visible = True
        eBook = eApp.Workbooks.Open(Filename:="C:\a.xls")
        eApp.Columns("A:A").Select()
        eApp.Range("A1:C1").Merge()
    End Sub
End Class

vadim63,
Try selecting after you have merged the cells. You should see the problem rberke is complaining about.
        eApp.Columns("A:A").Select()         'No problem yet if column A contains no merged cells
        eApp.Range("A1:C1").Merge()
       eApp.Columns("A:A").Select()          'Now you should see columns A:C selected

Brad

Very interesting!!  vadim63's code seems to work.  I will have to do a litte more playing and see where it leads.  I'll check back in tomorrow.

Goodnight to all

vadim63, thanks to you I have discovered a major misconception on my part.  

[a1:c3].Delete
[a1].EntireColumn.Select
[a1:c1].Merge                      ' this does NOT do the same thing
[a1:c1].MergeCells = True    ' that this does

The last line selects 3 columns, the second last line only selects one column.

I believe I am well on the way to solving my problem.  

Brad: I normally avoid select like the plauge. But, for this thread, just take it as an axiom that the sole purpose of my routine is to SELECT something.  

But, don't lose any sleep over this.  I can easily live without a solution, the only reason this is 500 points is because it is HARD.  Also, it bugs me when I can do something manually, but I can't do the same thing with a program..

One interesting discovery is that the mergeCells property automatically performs the Select method.

Do any Object Oriented gurus know if there is terminology for this?  Like maybe "passive property" vs "Active Property"?

Example to demonstrate this:
a: [a:x].Delete ' clean up earlier tests
b: [a:a].Select
c: [a1:c1].Merge
e: If Selection.Cells(1).MergeArea.Cells.count = 1 Then Debug.Print "does not print because cells(1) IS a merged cell. "
f: Debug.Print Selection.Columns.count  ' as expected, 1 column remains selected. this shows that .MergeArea.Cells.Count is a "passive property"
g: If Selection.Cells(1).MergeCells = False Then Debug.Print "does not print because cells(1) IS a merged cell. "
h: Debug.Print Selection.Columns.count  ' surprise!!! we now have 3 columns. This shows that .MergeCells is an "active property"

Anyway, thanks to you all, I have a solution. Two key concepts
Concept 1. Save the merged cells info, unmerge them and restore them at the end (First mentioned by elrondct and reinforced by several others)
Concept 2. use .merge  instead of .mergecells = True   (First mentioned by vadim63)

vadim63 will get the accepted answer because I had spent several hours with concept 1 and was not happy with the results.

Point splitting will be done at the end of the day.  Everybody will get at least 20, and I hope I don't offend anybody.

Here is my current code for anybody who is interested.   (I got carried away trying to Activate to "best" cell. Most of that code could easily be omitted.)

Sub extendColumns()
' first do a selfind to find cells which contain "target"
' this will create a scattered selection of individual cells.
' then call extendColumns() so each cells entire Column will be selected.
' then edit delete if you want them to be blasted.
Dim Mergetblix As Long
Dim MergeTbl() As Object

Dim area As Range, cl As Range
Dim i As Long
Dim allColumns As Range
Dim UsedRng As Range

Set UsedRng = Selection.Parent.UsedRange
Set allColumns = Selection.EntireColumn

Dim lastarea As Range
Dim cntColumns
On Error Resume Next
For Each area In Intersect(UsedRng, allColumns).Areas
    If err > 0 Then
        Set lastarea = UsedRng.Cells(1)
        cntColumns = 1
        Exit For
    End If
    Set lastarea = area
    cntColumns = cntColumns + area.Columns.count
Next area
On Error GoTo 0



allColumns.Select

If Selection.Columns.count = cntColumns Then
    allColumns.Cells(1, 1).Activate
Else
    For Each area In allColumns.Areas
        If area.MergeCells = False Then ' area is entirely unmerged, so skip it
        Else
            For Each cl In area
                If cl.MergeCells = False Then ' this cell is unmerged, so skip it
                Else
                    If cl.MergeArea.Address = Intersect(cl.MergeArea, allColumns).Address Then ' the cell is a subset of the extended selection, so skip it
                   
                    Else
            ' for all other merged cells, save their area then unmerge them
                        Mergetblix = Mergetblix + 1
                        ReDim Preserve MergeTbl(Mergetblix)
                        Set MergeTbl(Mergetblix) = cl.MergeArea
                        cl.MergeArea.UnMerge
            ' if we have unmerged the last merged cell in the area, skip the rest of the cells
                        If area.MergeCells = False Then GoTo nextarea
                    End If
                End If
            Next cl
        End If
nextarea:
        Set lastarea = area
    Next area
' select allColumns without getting too many Columns
    allColumns.Select


' and now we restore the merged information
    For i = 1 To Mergetblix
        MergeTbl(i).Merge
    Next i


    ' Note, the ActiveCell.Column will NOT be max(allColumns.Column).
    ' Instead it will be taken from the last Column in the order they were selected
    ' this is consistent with Excel's normal handling of scattered selections
    '   (for instance ctrl click on a5 d5 b5  then ctrl space. Excel puts active cell in Column b)
   
    ' Also Excel handles a click a column header by activate the first UNMERGED cell in column
    '
    ' The following code tries to be consistent with both of the above behaviours
    ' but, if lastarea is outside of used range it screws up.
    For Each cl In lastarea.Cells
        If cl.MergeArea.Cells.count = 1 Then
            cl.Activate
            GoTo activated
        End If
    Next cl
    Error 0  ' this should never happen
activated:
End If
End Sub

A similar routine called ExtendRows is identical except a global change of "column"  to "row"

Here come the points.

By the way, I found a few minor problems with me routine.  I'll post corrections in a few days.

(and yes, that is a neat idea Brad, but I think it will have to wait for the next project)

rberke - Thanks for the points - Patrick

Thank you, rberke.