Unable to filter properly because of merged cells

[Sebastiaan0031]

Hi all, my name is Sebastiaan of Holland and i could sure use your help!!!
Thanks for taking some of your time to give me some advice, very
apreciated!!!!

To summarize my problem: I want to export a huge database, and then start a
macro on it which will select the data according to country and put it on
different worksheets per country. However, the way the database is being
exported out of Siebel Analytic, it is impossible for me to filter the
countries as I THINK, the column in which the country is stated has been
merged.

More indepth:
For my work I want to create a model where I can export a huge database out
of Siebel Analytics into Excel, put a macro on it and run it every month to
put in a report.

After exporting the database, I want to put a filter on all the collumns
(A-G), select column A (countries) and select only the European countries.
(The export has all the worldwide countries in it).

The problem I have is that when I put a filter on the database, and select
the country (column A) and select Austria (the whole data range of Austria is
73 lines), only 1 line of the 73 lines shows up.

This is probably because the database and the country column is merged, as i
can see because this button is lighted (next to right allignment). The word
Austria is in line 31, but the 5 companies (Column B) have 73 products
(Column C) divided amongst them).

I would like to select the country in the filter and all the products show
up of this country.

I have found an alternative way to do it, by de-merging the cells (I clicked
button next to right alignment in taskbar up in the screen) and then copy the
country name over the rest of the lines. Then I will get directly the result
I want. However, this is not a good way, as when i record my macro this
method wont work (because every month, there are a different amount of lines,
for example 20/35/67/80 per country. And when I set up and record my macro he
will remember the precise amount of lines and will leave out or include other
lines, the next month I am running it again).

Ideal would be to press select all or select only column A, do something
with the excel sheet and then put a filter on it. Do you have an advise?

I hope you understand and that your excel knowledge is better than mine
(which I know for sure). I hope you can help me.

The best regards,

Sebastiaan

 

[Bob Phillips]

Good morning Sebastiaan,

Here is some code the will de-m erge the cells and copy the country names
down

Sub Test()
Dim iLastRow As Long
Dim i As Long, j As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").MergeCells Then
Cells(i, "A").MergeCells = False
End If
Next i
For i = 2 To iLastRow
If Cells(i, "A").Value = "" Then
Cells(i, "A").Value = Cells(i - 1, "A").Value
End If
Next i

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)