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.
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
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?
Try
Code: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
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