Cannot show merged cells

[lamdt]

Hi Guys,
I tried to modify the excel file has been published by VijianSale-Database-V4.1.1-Ask.xlsm which use excel as database and can easy to filter information of Data sheet on view sheet using Update drop down then search something in box and click on show data to get the result.
I got issue when the data in columns named PO, Quotation, VAT invoice are merged as below:
Merged Cell.png
When i click Update drop down on View sheet then the error Type mismatch will appear. I know that now the function of Show Data cannot get the Cells that not have data. Could someone help me to change the code to permit show data can work even the cells have been merged ?

[SamT]

Only rank amateurs and extremely experienced Pros dare to use Merged Cells.
Since an experienced pro would not have this problem, all I can say is "Don't use Merged Cells!"

[lamdt]

Hi SamT,
Thank you for your information.
My employees send weekly report to me which they have been merged information at PO -Quotation - VAT invoice cause it easy to them to follow their daily work and i do not want to request them to change the way they made report file. Is there any way to take data from data sheet with merge cells information or could you guide me how to un-merged cells when click on Update Drop down ? I think about this logic:
1. When click on update - check data
2. If data > 0 then do as code.
3. If data =0 (merge cell) then un-merge, copy information form merge cell and paste to others.
4. Show data
is it possible?

[SamT]

VBA code: Sheets("Sheet1").Cells.Unmerge

Do it manually. It only takes a few seconds.

[lamdt]

The problem is that the real data file is quite big (about 10000+ lines) and we keep update everyday. If VBA can do as i think then everyone just insert their report to the shared data file without check and unmerge all merged cells.

[Paul_Hossler]

Only rank amateurs and extremely experienced Pros dare to use Merged Cells.
Since an experienced pro would not have this problem, all I can say is "Don't use Merged Cells!"

Bit harsh

Merged cells do have their place if used correctly and carefully

It's easy enough to unmerge them and to enter the 'merged value' into each of the cells to normalize (standardize?? fix??) the table

Downside is that it might add a little to the run time

Option Explicit


Sub FindMergedCells()
    Dim r As Range, r1 As Range


    Application.ScreenUpdating = False
    
    For Each r In ActiveSheet.UsedRange
        If r.MergeCells Then
            Set r1 = r.MergeArea
            r.UnMerge
            r1.Value = r.Value
        End If
    Next


    Application.ScreenUpdating = True


End Sub

[SamT]

@ lamdt

then everyone just insert their report to the shared data file without check and unmerge all merged cells.

We will need to see the code you use to insert the new data into the shared data file

Me may have to use my snippet above and unmerge the entire sheet, or we may be able to use Paul's method on just the new data.

[lamdt]

Hi Paul,
I tried to make the Button and add your code there but it does not work.
I tried this file to add Unmerged cells to the Unmerged Merge button it work with row 3-26 but did not work with other, could you guys check the code and guide me what is problem here ?
Sale-Database-V4.1.1-Ask VBAexpress.xlsb
Thank you so much.

[lamdt]

Hi SamT,
I copy new data from report file to data base manually.

[SamT]

VBA code: Sheets("Sheet1").Cells.Unmerge

Do it manually. It only takes a few seconds.

---------------------------------------------------------------------------
Sir or Ma'am,
We are not watching you over your shoulder. We need much bore specific information than "I do it manually."

[lamdt]

Hi SamT,
Not only one employee send weekly report to me but a team. I know that i can do it manually without problem but i think VBA can do it for me, am i right ?

[SamT]

VBA can do it for me, am i right ?

Probably yes.

But we still need to know very specific details how you get data and enter data.
Step be step details; For example:

1. Open Workbooks named ...
2. Open Sheet named ...
3. Select Range A1:Z99
4. Copy Range A1:Z99
5. Open Workbook Sale-Database
6. Open sheet Data
7. Select Cell A1
8. Paste data copied from sheet... Range A1:Z99

We will also need to know what cells are merged. Are they always the same cells?. On what sheet?

[lamdt]

Hi SamT,
Thank you for your help.
My employees they manage the Sale database by themselves which have 03 important information are: PO , Quotation, VAT Invoice. Normally, data in these columns always merged for easy view. They send file to me as the report weekly and i want to build a sale database which contain date of all sale members. With View sheet i provide them 03 filter information related to PO, Quotation and VAT invoice, they can search and get information of each other.
Normally, after they send file to me, i will do the manually copy all their information and put on data sheet (i just think about make separate sheet for each members but do not know how to search data from multi-sheet), the procedure of copy data is that:
1. Open workbooks named: Sale report W45, W46 ...
2. Open Sheet named: Hien, Oanh, Anh, Thuy ...
3. Select Range: A3:LX (based on each file X maybe 20 or 50)
4. Open workbook Sale-Database
5. Select cell Ax (x is current ending row of last week)
6. Paste data copied above.

By the way, could you guide me how to filter data of multi-sheet, i mean:
1. Click Update drop down (check data of sheet named: Hien, Oanh, Anh, Thuy not only Data)
2. Insert filter information.
3. Click show data-> the result will have information take from all sheets named: Hien, Oanh, Anh, Thuy.
if i can do that then i do not need to copy report of members to data sheet as currently

[SamT]

I am not sure that you and I mean the same thing with "merge"

Please upload an original sheet from an employee, a sheet that still has merged data or merged cells on it. Highlight the merging.

[Paul_Hossler]

I am not sure that you and I mean the same thing with "merge"

Please upload an original sheet from an employee, a sheet that still has merged data or merged cells on it. Highlight the merging.

From OP's #1 -- It's hard to see but the XLSM reflects the picture in the post

I tried to modify the excel file has been published by VijianSale-Database-V4.1.1-Ask.xlsm which use excel as database ...

[Paul_Hossler]

This was not my code

Private Sub UnMerge_Merged()
    With Sheets("data")
        a_max = .Cells(.Rows.Count, "A").End(xlUp).Row
        For x = 1 To 12
            For a = 3 To a_max
                With .Cells(a, x)
                    If .MergeCells Then
                        ax = .Value
                        m = .MergeArea.Count
                        .UnMerge
                        For am = a To a + m - 1
                            With Sheets("data").Cells(am, x)
                                .Value = ax
                                .Interior.ColorIndex = 6
                            End With
                            DoEvents
                        Next am
                    End If
                End With
            Next a
        Next x
        MsgBox "Done"
    End With
End Sub

Look at the attachment

[lamdt]

Hi Paul,
Thank you for your attention.
I tried your code, i import it to the Update drop down but it does not work

[Paul_Hossler]

You have Form controls on your View sheet. Form control macros go in a standard module, not in the worksheets code module. ActiveX controls have their code in the worksheet module

I don't know about the rest of what you've got, but if I put the Unmerge macro onto a standard module (1) and on View sheet specify that macro (2), then it works



(1)
Capture2.JPG


(2)
Capture.JPG


This is to only run the Unmerge sub, NOT the rest since I don't have anything to test with

[lamdt]

It worked Paul, thank you very much but could you add the function like: click 1st time it with unmerge all merged cells, click 2nd time it will merged cells with same information ?