Solved: Select a column with merged cells

[ericc]

Hello

I use your site since some weeks, and I until now always find an answer to my question, just by searching in the forum or the kb, but now I can't, may be because I don't know how to search ...

It's a simple and stupid problem.
I use excel 2000.
I would like to select a column through VBA with this simple code :
[vba]Cells(1, 10).EntireColumn.Select[/vba] and then search in the selection with a "for each" loop.

The problem is that the column 10 contain some (horizontally) merged cells !!
So when I select the column, it select also the adjacent column
I try almost every thing I can imagine but it still select more than one column ...

Do you have an idea for me ?

thanks in advance for your help

ericc

[Zack Barresse]

Hi there, welcome to the board!

1) Do not select
2) Do not use merged cells

If you agree to these, please tell us the entire scope of your application, i.e. what it is you are trying to accomplish. Note that I say if you agree. So, if you do not, does this mean nobody will help you? No. They're just two very strong recommendations that will make your life easier.

[Norie]

eric

Well the first idea that springs to mind is to get rid of the merged cells.

[figment]

i accordance to firefytr's first rule this dosent use selects, however it dosn't care about mirged cells.

[vba]Sub testing()
Dim ra As Range, ra2 As Range
Set ra = Cells(1, 10).EntireColumn
For Each ra2 In ra
'ra2.Offset(0, -4).Value = ra2.Value
'your code here. above is my test code
Next
End Sub[/vba]

this will treat all merged cells as blank, unless the uper left cell of the mirged group is in column 10, then it will treat just that cell as if it has data.

[ericc]

Hi

Thanks for yours fast answers

1) Do not select

I don't understand what you mean !!

2) Do not use merged cells

Yes, I think it will be the only solution

If you agree to these, please tell us the entire scope of your application, i.e. what it is you are trying to accomplish. Note that I say if you agree. So, if you do not, does this mean nobody will help you? No. They're just two very strong recommendations that will make your life easier.

I understand that, but it's a file containing some confidential (professional) information, and I can't disclose it.

To explain quickly, I have several tables in one sheet, and I make a macro to add a column to each table in one action.
The header of the each table is a merged cell.
After adding the column, I need to do some "cosmetic" action on certain cells, this is the reason of the "for each" loop.
To be a little bit more complicated, all table contain the same number of row, but this number may vary and not all the cells contain a value ....

ericc

[ericc]

i accordance to firefytr's first rule this dosent use selects, however it dosn't care about mirged cells.

So you mean that I don't need to select the range to be able to loop in it with "for each" !! Ooops

[vba]
'ra2.Offset(0, -4).Value = ra2.Value
[/vba]
Is this line needed ?
As you example doesn't work for me ...

[vba]Dim ra As Range, ra2 As Range
Set ra = Cells(1, 10).EntireColumn

For Each ra2 In ra
If ra2.Interior.ColorIndex = 36 Then
ra2.Borders(xlEdgeRight).Weight = xlMedium
End If
Next
[/vba] it's even don't go through the loop

ericc

[figment]

you need to define a line style befor the border will show up.

[VBA] With ra2.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With[/VBA]

[ericc]

you need to define a line style befor the border will show up.

Thanks for your help figment
But the problem is not there !!

This line work as I use it at several place in the code.
But the fact is that the code never reach this line as it seems to test only the first cell of the column and don't loop.

ericc

[figment]

i can thing of a few resons;

1. only the first cell has an interior pater color of 36, the other might be close in color but are not actuly that color

2. the worksheet needs to be defined, and the code is looking at the wrong sheet(although if its testing the first cell this is unlikly)

3. the entirecolumn property is not actuly the enire column, it is instead extend down, and as such stops at the first blank cell (again unlikly)

thats all i can think of at the moment, if you could post a sample worksheet i could be of more help.

[Zack Barresse]

ericc, just explain the complete dynamics of your tables if you cannot post a sample of them. I would ask if you could dummy up the data and post a small representative sample anyway. We don't need data if you are only worried about formatting, just where the data is and where it could go.

[ericc]

All

I made it in an other way !!
Instead of selecting all the column, and because the merged cell is only on the first row, I select a range from here.
[VBA]range(cells(2,10),cells(lastrow,10)).select[/VBA]
And it works nicely

When I did :
[VBA]Set ra = Cells(1, 10).EntireColumn[/VBA]
ra.count give me 1 !! This is why it doesn't go through the loop !!

Thank you for your help and your advise

ericc