How can I unmerge cells, delete empty col and rows, relocate cells and combine?

Solved/Closed
alhaddadi Posts 14 Registration date Wednesday February 8, 2017 Status Member Last seen July 13, 2020 - Jun 22, 2020 at 10:13 AM
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 - Jul 16, 2020 at 11:34 AM
Hi dears,

first of all, I have to thank every one who read this message and he try to help me.

its a complicated matter to me.

Actually, I received Excel workbook, and there are 88 worksheets, this data is very sensitive, such as SN, Part No, Item Name, ... etc.

He request from me to combine all sheets in one sheet.

i search in google to solve this task but I don't know how to start to find the result.

I think after Analyzing it needs:
1. When I tried to combine data it says: can't combine because there are merge cells. So, how can I unmerge all data at one time.
2. How can delete all empty columns and rows at one time.

I hope you understand and to fine the answer here or advice me if you don't mind.

thank you again

4 replies

TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Updated on Jun 25, 2020 at 12:07 PM
Hi Alhaddadi,

Always happy to help someone who appreciates the effort!

I forgot to ask if you are familiair with using codes, so below you will find the code and below that an explanation of how to use it.

You should end up with a sheet named "Main", where all the data is assembled. I bolded and locked the header as well as autofitted the columns.

As for question 5, it is better to have all the data on a row for further data processing. For example, when you filter your data for Maker: Sony, you will want to see the data in the first 9 columns as well.

Here is the code:
Sub RunMe()
Dim sh As Worksheet, mHeader As Boolean, mR, sR As Long, mC, sC As Integer

Application.ScreenUpdating = False

Sheets.Add before:=Sheets(1)
ActiveSheet.Name = "Main"

mR = 2

With Sheets("Main")

    For Each sh In Worksheets
        sh.Select
        If sh.Name <> "Main" Then
            If mHeader = False Then
                .Range("A1").Value = Range("A2").Value
                .Range("B1").Value = Range("C2").Value
                .Range("C1").Value = Range("E2").Value
                .Range("D1").Value = Range("A4").Value
                .Range("E1").Value = Range("C4").Value
                .Range("F1").Value = Range("E4").Value
                .Range("G1").Value = Range("A6").Value
                .Range("H1").Value = Range("C6").Value
                .Range("I1").Value = Range("E6").Value
                .Range("J1").Value = Range("A9").Value
                .Range("K1").Value = Range("C9").Value
                .Range("L1").Value = Range("E9").Value
                .Range("M1").Value = Range("G9").Value
                mHeader = True
            End If
        
            .Range("A" & mR).Value = Range("A3").Value
            .Range("B" & mR).Value = Range("C3").Value
            .Range("C" & mR).Value = Range("E3").Value
            .Range("D" & mR).Value = Range("A5").Value
            .Range("E" & mR).Value = Range("C5").Value
            .Range("F" & mR).Value = Range("E5").Value
            .Range("G" & mR).Value = Range("A7").Value
            .Range("H" & mR).Value = Range("C7").Value
            .Range("I" & mR).Value = Range("E7").Value
            
            .Select
            Range(Cells(mR, "A"), Cells(mR, "I")).Copy .Range(Cells(mR + 1, "A"), Cells(mR + 6, "I"))
            
            sR = 10
            sC = 1
            mC = 10
            
            Do
                Cells(mR, mC).Value = sh.Cells(sR, sC).Value
                sC = sC + 2
                mC = mC + 1
                
                If sC = 9 Then
                    sR = sR + 1
                    sC = 1
                    mR = mR + 1
                    mC = 10
                End If
            Loop Until sR = 17
        End If
    Next sh
End With

Rows(1).Font.Bold = True
Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True

Application.ScreenUpdating = True
End Sub


How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.


Best regards,
Trowa
3
alhaddadi Posts 14 Registration date Wednesday February 8, 2017 Status Member Last seen July 13, 2020
Jun 28, 2020 at 11:17 AM
You are soo kind,
I will try this and I will let you know.

Thank you
0
alhaddadi Posts 14 Registration date Wednesday February 8, 2017 Status Member Last seen July 13, 2020
Jul 3, 2020 at 09:51 AM
Dear Trowa,

It's a nice code and it working very fine.

I am not that much good with codes, but I try to read and understand what is written

I appreciate your effort and time that I took for me. Did you know that with this code you saved me a lot of time that I will waste in collecting this information page by page I have to collect 7,425 sheets really it will take weeks or maybe months.

Many thanks dear.
0
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525 > alhaddadi Posts 14 Registration date Wednesday February 8, 2017 Status Member Last seen July 13, 2020
Jul 6, 2020 at 11:16 AM
That is awesome. Glad I could help you!
0
alhaddadi Posts 14 Registration date Wednesday February 8, 2017 Status Member Last seen July 13, 2020
Jul 13, 2020 at 04:11 AM
Hi Mr. Trowa,
hope you fine

dear, I did some change in my table,
from 1-9 are perfect.
from 10-13 I make change on it.
the final picture is (all cells one Row from A1- AK2)
I understood the code steps from A1-A9 only :). however, the rest its bit hard.

I hope you get my idea in final clear picture.
it is possible to teach me how can I change the code whenever I want?
OR
you can do easy code for me to learn step by step.

Thank you so much for your patience.

BR
0
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Jun 23, 2020 at 12:10 PM
Hi Alhaddadi,

Thanks for the clear answers. I'll get back to you on Thursday.

Best regards,
Trowa
2
alhaddadi Posts 14 Registration date Wednesday February 8, 2017 Status Member Last seen July 13, 2020
Jun 23, 2020 at 02:15 PM
Hello Trowa,

I am very happy with your prompt response to me and I appreciate that effort that you save for me.

Many thanks
0
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Jun 22, 2020 at 12:04 PM
Hi Alhaddadi,

You came to the right place!

The best and easiest way would be to use a VBA code. But for me to provide you one, I will need to understand your workbook and sheets setup.

Do all your sheets ALWAYS have:
1) Range A1:H7 for points 1 to 9?
2) Points 10 to 13 start on row 9?
3) 6 names (A10 to A15) or is this variable?

4) Are there any other sheets in your workbook that needs to be excluded from this combining action? If so, which are their names?

5) Your presented table of relocated data looks fine to me. Although, some people might want to put the data from A19 to I19 also in A20 to I24. Let me know what you want.

Let me know the answer to these 5 questions.

Best regards,
Trowa
1
alhaddadi Posts 14 Registration date Wednesday February 8, 2017 Status Member Last seen July 13, 2020
Updated on Jun 22, 2020 at 01:30 PM
Hi Trowa,
many thanks to you for cute welcome msg.

Do all your sheets ALWAYS have: around 88 sheets in exactly same format
1) Range A1:H7 for points 1 to 9? ===============> "A1:G7"
2) Points 10 to 13 start on row 9? ===============> "YES"
3) 6 names (A10 to A15) or is this variable? =======> 7 names "A10:A16"
4) Are there any other sheets in your workbook that needs to be excluded from this combining action? If so, which are their names? ===============> "NO"
5) Your presented table of relocated data looks fine to me. Although, some people might want to put the data from A19 to I19 also in A20 to I24. Let me know what you want. ===============> "It's fine for me, you have more experience than me, I need your advice"

Thanks for your help.
0
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Jul 16, 2020 at 11:34 AM
Hi Alhaddadi,

As per your request I adjusted the code. I added some text to hopefully help you understand what is going on. Let me know what is beyond your understanding, so I can explain further. Always happy to help someone getting to know VBA.

Here is the code:
Sub RunMeV2()
Dim sh As Worksheet, mHeader As Boolean, mR As Long, mC, sR, sC, x As Integer

Application.ScreenUpdating = False

Sheets.Add before:=Sheets(1)
ActiveSheet.Name = "Main"

mR = 1 'Main Row
mC = 1 'Main Column
sR = 2 'Sheet Row
sC = 1 'Sheet Column
x = 1 'Counter to add to the header of Sheets row 9

With Sheets("Main") 'Whenever a line starts a . we are referring to main sheet
    For Each sh In Worksheets
        sh.Select
        If sh.Name <> "Main" Then
            If mHeader = False Then
                Do
                    .Cells(mR, mC).Value = Cells(sR, sC).Value 'Cells(row,column)
                    mC = mC + 1
                    sC = sC + 2
                    If sC = 7 Then
                        sC = 1
                        sR = sR + 2
                    End If
                Loop Until mC = 10
                    
                sR = 9
                    
                Do
                    .Cells(mR, mC).Value = Cells(sR, sC).Value & x
                    mC = mC + 1
                    sC = sC + 2
                    If sC = 9 Then
                        sC = 1
                        x = x + 1
                    End If
                Loop Until x = 8
                mHeader = True
            End If
            
            mR = mR + 1
            mC = 1
            sR = 3
            sC = 1
                        
            Do
                .Cells(mR, mC).Value = Cells(sR, sC).Value
                mC = mC + 1
                sC = sC + 2
                If sC = 7 Then
                    sR = sR + 2
                    sC = 1
                End If
            Loop Until mC = 10
        
            sR = 10
            sC = 1
            mC = 10
            
            Do
                .Cells(mR, mC).Value = sh.Cells(sR, sC).Value
                sC = sC + 2
                mC = mC + 1
                
                If sC = 9 Then
                    sR = sR + 1
                    sC = 1
                End If
            Loop Until sR = 17
            mC = 10
        End If
    Next sh
    .Select
End With

Rows(1).Font.Bold = True
Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True

Application.ScreenUpdating = True
End Sub


Best regards,
Trowa
0