VBA Code and merging cells

[siddhu1234]

Hai guys ,

I am working a new store management for my form , in this light i have created a few user forms to capture the data into excel,while doing so i was badly struck at one point if you see the below excel sheet containing the column heads as below

Supplier Name	Supplier Address	City	Country	Postal Code	Mail ID	Fax No	Mobile No	Contact person	Designation	Material
										fsdfsdf
										fsdfsdf
										fsfsdfs
M/s Makrocast	sfsdfsdf	sfsdfsdf	dsdfsdf	32323​	Siddhuprince@gmail.com	8878​	8768786​	hjghsgjhaf	sdfsdfsfd	sdfsf

If we see here these are the details i have captured from the user-form which i have created , when you see the above excel i have the material column which is having details of the different materials, but the supplier details or any other details have been captured in one row , here how can i use a VBA code to merge the empty rows as above under individual column,
This sequence will be happening every 4 rows once , i am sending my excel sheet also for reference

 

[Anthony47]

I recommand that you DON'T merge cells, but you fill the rows with the full information.
You can do that with the following macro:

Sub Normalize()
Dim DaCol As String, LastFull As Range
'
DaCol = "A"                     '<<< "Supplier Name" column
Application.EnableEvents = False
For I = Cells(Rows.Count, DaCol).End(xlUp).Row To 2 Step -1
    If Cells(I, DaCol).Value = "" And Cells(I, DaCol).Offset(0, 10) <> "" Then
        If Not LastFull Is Nothing Then
            LastFull.Copy Cells(I, DaCol)
        End If
    Else
        Set LastFull = Cells(I, DaCol).Resize(1, 11)
    End If
Next I
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

This will work on the active sheet; the macro will start from the last compiled row in the specified column (see the line marked <<<), and will go backward coping its data to the previuos empty rows. This will work an the whole worksheet, not only on the last block.
If you wish, you may call this macro at the end of your process, adding the line Call Normalize to your code

Bye