CODEWITHSUNDEEP
vbaexcel
Sukanya
Sukanya

Reputation: 43

Macro code to get the merged cell value along with other cell value

I have attached the input file format with sample data here. Can someone help with the macro code to get the output data as mentioned below.

Input file for Macro :

Roll No Name    Mark1   Mark2
1       Abc      10      35
                 20      25
                 30      40
2       def      20      10
                 15       5
                 25       2
                 30       3

The merged cells data needs to be obtained in separate rows along with marks data.

Output Data to be obtained : 

Roll No Name    Mark1   Mark2
1       Abc       10    35
1       Abc       20    25
1       Abc       30    40
2       def       20    10
2       def       15    5
2       def       25    2
2       def       30    3

Upvotes: 0

Views: 221

Answers (2)

Slai
Slai

Reputation: 22866

Easier to fill the blanks with a formula:

Dim r As Range
Set r = Range("A1").CurrentRegion
r.UnMerge
r.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

Upvotes: 0

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Check this out,

enter image description here

Sub unMerge()
Dim i As Long, strA As String, strB As String
Range("A:B").unMerge
strA = Cells(2, 1)
strB = Cells(2, 2)
For i = 2 To Cells(Rows.Count, 4).End(xlUp).Row
    If Cells(i, 1) = "" Then
        Cells(i, 1) = strA
        Cells(i, 2) = strB
    Else
        strA = Cells(i, 1)
        strB = Cells(i, 2)
    End If
Next i
End Sub

Upvotes: 1

Related Questions