Problem on filter with merging cell

[Blanko]

Excel version: 2010

Currently having problem on showing all my data. The screenshot below shows my objective of filtering 'Completed'. However, there are action that are being cut and only shows the 1st row. My objective is to show all the data when being filter accordingly.


1:



2:

 

[Blanko]

Which column is that? Also, I should have specified in my previous post that the column needs to not have merged cells. As you've found out by trying to filter with merged cells, only the first (top left) cell in a range of merged cells holds a value.

It will be another status column showing 'Completed', 'Overdue' and 'In Progress'.

 

[DushiPunda]

I meant what column is it? Column E? F? G? If you have 250 record sets, each of them taking up a theoretical 4 rows, can you assure that, this column from row 1 to row 1,000 does not contain any blank cells or merged cells, and does not have any data beyond your last record set?

 

[jolivanes]

Try

Sub Merged_Cell_Problem()
Dim c As Range
Application.ScreenUpdating = False

    For Each c In Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
        With c
            If .MergeCells Then
                If c.Value <> "" And c.Value <> "Completed" Then
                    .MergeArea.EntireRow.Hidden = True
                End If
            End If
        End With
    Next c
    
Application.ScreenUpdating = True
End Sub

 

[Blanko]

I meant what column is it? Column E? F? G? If you have 250 record sets, each of them taking up a theoretical 4 rows, can you assure that, this column from row 1 to row 1,000 does not contain any blank cells or merged cells, and does not have any data beyond your last record set?

Column AI. There will be blank cells for every row. It also contain data beyond my last record set.

 

[Blanko]

Try

Sub Merged_Cell_Problem()
Dim c As Range
Application.ScreenUpdating = False

    For Each c In Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
        With c
            If .MergeCells Then
                If c.Value <> "" And c.Value <> "Completed" Then
                    .MergeArea.EntireRow.Hidden = True
                End If
            End If
        End With
    Next c
    
Application.ScreenUpdating = True
End Sub

The code will be in general or worksheet tab? Sorry I am not familiar with it.

 

[DushiPunda]

Should be installed in a standard module. ALT + F11 to open VBA. Assuming you don't have any modules installed already, right click on the left side, go to Insert, and choose module. Then copy and paste the code. If you want to quickly try it out and see if it works, save your workbook as another name so that you don't mess with your actual data, then you can click on any line within the code and press F5 on your keyboard. Then go to your workbook/sheet and see if you got the desired results you're looking for.

 

[jolivanes]

Please don't quote every post. It makes a mess of the thread.

Or you could run this and use AutoFilter after.
It gets rid of the merged areas and fills the empties.
Try it on a copy of your original if you want to try it.

Sub UnMerge_And_Fill()
Dim c As Range, r As Range, cel As Range, i As Long
For Each c In ActiveSheet.UsedRange
With c
 If .MergeCells Then
    With .MergeArea
        .UnMerge
    End With
 End If
End With
Next c

For i = 3 To 1 Step -1
    Set r = Columns(4).SpecialCells(xlCellTypeConstants)
        Set r = r.Offset(, -i).SpecialCells(xlCellTypeBlanks)
            For Each cel In r
                cel.Value = cel.Offset(-1).Value
            Next cel
Next i
End Sub