Filter with merged cells maybe there is an alternative way

Hi everybody, as my account name, I am new here.

Straight to my problem

I am organizing an audit summary.
For each company, there are 10 rows, and the first few columns (say A, B,and C), they are merged for COMPANY NAME, LOCATION, NUMBER.
Then from column D, it starts revision records, which means 10 rows may or may not be filled completely. Some may be filled up to the 5th row, and some are only 2nd row.

So later when I am trying to filter by those who are in the 2nd revision, problem comes. Autofit doesn't work which is expected, but also the entire worksheet looks not nice.

What I would like to have is to filter on the revision column, but no mater how many rows are filled in, it will be nice if all 10 rows for the companies that satisfy the 2nd revision to show up as the entire 10 rows.

Any one could provide me with a good idea ?

big thanks

Hello Iamnew,

Welcome to the Forum!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi Iamnew,

I need more explanation - I'm not noticing a great difference in what you want to achieve from what you have

For example, the original "project" column merges 7 row.
After the filter on Column E "result"
Is it possible to keep the "project" column with merged 7 rows but only eliminate the "x" "active" "fail" and projects that do not contain "pass"

You can't do that, you need to repeat the data for each row. The nearest I can think of to get to the layout you want is to have the data in every row and then a Pivot table summarising it (with not values) - would look like the attached

Maybe:

Sub Iamnew(): Dim we As Worksheet, wa As Worksheet, r As Long
Set we = Sheets("Example sheet ") 'Trailing Space in Sheet Name
Worksheets.Add(After:=Worksheets(Worksheets.count)).Name = "to achieve"
Set wa = Sheets("to achieve"): we.Cells.Copy wa.Cells(1, 1)
For r = 3 To Rows.Find("*", , , , xlByRows, xlPrevious).Row
If Cells(r, 6) = "" Then Rows(r).Hidden = True
Next r: End Sub

In what ways does it not work? If you'll enumerate them, I may figure out another "work-around"

But, if you only want to see the records with "pass" in Column E, then the last routine which I added to post #13 will do that.

Is the sample truly representative?

Meanwhile try this:

Sub Iamnew(): Dim we As Worksheet, wa As Worksheet, r As Long, H As Range, E
Set we = Sheets("Example sheet ") 'Trailing Space in Sheet Name
Worksheets.Add(After:=Worksheets(Worksheets.count)).Name = "to achieve"
Set wa = Sheets("to achieve"): we.Cells.Copy wa.Cells(1, 1)
E = wa.Range("E1:E" & wa.Rows.Find("*", , , , xlByRows, xlPrevious).Row)
For r = 3 To UBound(E)
If LCase(E(r, 1)) <> "pass" Then
If H Is Nothing Then Set H = Rows(r) Else Set H = Union(H, Rows(r))
End If
Next r: H.EntireRow.Hidden = True: End Sub

Yes it is.

the difference is

1. there are more columns with various input types.
2. more rows standing for more companies.

but in the sample I have neat input, while in the real one, some text/symbol/garbage are put in the cells that belong to "date" or "rev." columns. it s vast and confidential, so cannot copy that here.

I put this code in a standard module which I inserted into your book and I saved your book as macro enabled:

Option Explicit

Sub Iamnew(): Dim we As Worksheet, wa As Worksheet, r As Long, H As Range, E
        For Each wa In Worksheets
        wa.Name = Trim(wa.Name)
        If wa.Name = "to achieve" Then GoTo Setwa
        Next
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "to achieve"
Setwa:  Set wa = Sheets("to achieve"): Set we = Sheets("Example sheet")
            we.Columns.AutoFit: we.Cells.Copy wa.Cells(1, 1)
E = wa.Range("E1:E" & wa.Rows.Find("*", , , , xlByRows, xlPrevious).Row)
                For r = 3 To UBound(E)
                    If LCase(E(r, 1)) <> "pass" Then
If H Is Nothing Then Set H = wa.Rows(r) Else Set H = Union(H, wa.Rows(r))
                    End If
Next r: H.EntireRow.Hidden = True: wa.Columns.AutoFit: End Sub

*You need to change the red sheet names to what they should be and then just copy
and paste your actual data to the "Example sheet" (The button in D1 should not be
affected by this) and then just click on the button!

** But I'm not sure if this is what you want