How Do i freeze cells?!

[KristyP]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Numbers
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]Merged
[/TD]
[TD]merged
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]merged
[/TD]
[TD]merged
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]merged
[/TD]
[TD]merged
[/TD]
[/TR]
</tbody>[/TABLE]
PROBLEM: I have a drop down menu in my data set i.e (numbers) and when I filter for 1 & 3 it hides my merged data in that row. I'd like to be able to filter without collapsing my merged cells.

Let me know if you need further clarification. THANK YOU!

 

[Gerald Higgins]

Can you explain more clearly please ?

How exactly is your data merged ?
Do you mean merged cells ?

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Michael M]

Another thing to consider is not using merged cells....especially when sorting or filtering.
Instead, highlight the cells to be merged then use>>Format cells>>Alignment>>Horizontal sropdown>>"Center across Selection"
It will appear to be merged, but won't affect other actions.

 

[KristyP]

Hi Gerald, so what I mean is where I wrote "merged" (C2:D4 in my example)....In excel I've hit merge & Center so all those spaces are actually just one...I just didn't know how to demonstrate that in a thread. When I filter for numbers 1 & 3 it hides row 2 and makes the merged space smaller. Basically i want this merged spaced to be like a textbox that doesn't change regardless of what i do my data. I can't make it a textbox because I have a formula in that merged area.

 

[Gerald Higgins]

Sorry but I think you're in an "either / or" situation here.

Merged cells can cause alot of problems, the one you are describing is just one of them but there are others.

I think your choices are
1) Use merged cells but don't use Filter
2) Use Filter but don't use merged cells
3) Use merged cells AND Filter, and accept the limitations that brings.

Does the area of merged cells HAVE to be in the Data / Filter area, or could it be somewhere else ?

 

[Michael M]

@KristyP
I think you should visit my method to avoid Merged Cells

 

[jolivanes]

If you are willing to take the good advice of the previous helpers, you could use this to change all the merged areas to center across selection.
If you're not sure, try on a copy of your original first.

Sub Change_Merged_To_Center_Across()
Dim c As Range
For Each c In ActiveSheet.UsedRange
With c
 If .MergeCells Then
    With .MergeArea
        .UnMerge
        .HorizontalAlignment = xlCenterAcrossSelection
    End With
 End If
End With
Next c
End Sub

 

[KristyP]

Hi jolivanes,

I don't have much experience using VBA. How would I go about using your code so Ranges (e1:v24) & (e24:v29) stay merged even if I filter?

Thank you!!

 

[Gerald Higgins]

I'm not a VBA expert, but I think jolivanes' solution does NOT keep ANY cells merged, it UNMERGES all merged cells on the active sheet (I think !)