How Do i freeze cells?!

KristyP

New Member
Joined
Dec 13, 2018
Messages
6
Numbers
1
Merged
merged
2
merged
merged
3
merged
merged

<tbody>
</tbody>
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Can you explain more clearly please ?

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,885
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
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.
 
Last edited:

KristyP

New Member
Joined
Dec 13, 2018
Messages
6
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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 ?
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
@KristyP
I think you should visit my method to avoid Merged Cells
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,417
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.
Code:
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

New Member
Joined
Dec 13, 2018
Messages
6
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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 !)
 

Forum statistics

Threads
1,082,280
Messages
5,364,210
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top