Unmerge Cells & Copy Cells Down to Next Line of Data

[t0mato]

Paul, that is exactly it. Just trying to wrap my head around the change here.

Thanks again!

[snb]

I used the file in your first post:

This is all you need for the first 4 columns:

Sub M_snb()
   Sheet1.Cells.UnMerge

   For Each it In sheet1.Columns(1).Resize(, 4).SpecialCells(4).Areas
      it.Value = it.Offset(-1).Cells(1).Resize(, it.Columns.Count).Value
   Next
End Sub

For the first 4 columns in all sheets in the active workbook:

Sub M_snb()
   For Each sh In Sheets
    sh.Cells.UnMerge
    
    If sh.Name <> "Instructions" Then
        For Each it In sh.Columns(1).Resize(, 4).SpecialCells(4).Areas
           it.Value = it.Offset(-1).Cells(1).Resize(, it.Columns.Count).Value
        Next
    End If
  Next
End Sub

NB. You should never use merged cells.

[Paul_Hossler]

@snb --

1. I like your more efficient way, but this below eliminates the .Areas loop

Option Explicit


Sub UnmergeAndFill()
    Dim rData As Range, rBlanks As Range
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "Instructions" Then GoTo NextSheet
        
        ws.Range("A:F").UnMerge
        
        On Error GoTo NextSheet
        Set rData = ws.Cells(1, 1).CurrentRegion
        Set rBlanks = rData.SpecialCells(xlCellTypeBlanks)
        rBlanks.FormulaR1C1 = "=R[-1]C"
        rData.Value = rData.Value
        On Error GoTo 0
            
        If Not ws.AutoFilterMode Then ws.Rows(1).AutoFilter


NextSheet:
    Next


    MsgBox "Done"
End Sub

2.

NB. You should never use merged cells.

IMVHO, 'Never' is mostly correct, but I'd agree that 97% of the time merged cells only cause trouble

[snb]

@PH

Yes it does eliminate.
But it takes more calculations.
I am not an adversary to loops, which I am to 'GoTo'-statements.
And almost equally to unnecessary Object variables:

 with ws.Cells(1, 1).CurrentRegion.SpecialCells(4)
   .Value = "=R[-1]C"
   .Value = .Value
 end with

[Paul_Hossler]

@PH

Yes it does eliminate.
But it takes more calculations.
I am not an adversary to loops, which I am to 'GoTo'-statements.
And almost equally to unnecessary Object variables:

 with ws.Cells(1, 1).CurrentRegion.SpecialCells(4)
   .Value = "=R[-1]C"
   .Value = .Value
 end with

1. Well, the only advantage I see to my 'unnecessary Object variables' is that my way works without generating a lot of #N/A errors

Capture.JPG

Using the original Test_Unmerge2.xlsx as input, and adding your .Value snippet to the Unmerging and error checking and "Instructions" test



2. I don't see any significant increase in calculations, and I've found that sometimes, and in very specific circumstances, a GoTo can make code more readable without turning it into a plate of spaghetti

Sub UnmergeAndFill_snb()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "Instructions" Then GoTo NextSheet
        
        ws.Range("A:F").UnMerge
        
        On Error GoTo NextSheet
        
        'snb -----------------------------------------------------------
        With ws.Cells(1, 1).CurrentRegion.SpecialCells(4)
            .Value = "=R[-1]C"
            .Value = .Value
        End With
        'snb -----------------------------------------------------------
        
        On Error GoTo 0
            
        If Not ws.AutoFilterMode Then ws.Rows(1).AutoFilter
NextSheet:
    Next
    
    MsgBox "Done"
End Sub

[snb]

Here you go:

Sub M_snb()
  For Each it In Sheets
    If it.Name <> "Instructions" Then
      it.Cells.UnMerge
        
      With it.Cells(1).CurrentRegion.Resize(, 4)
        .SpecialCells(4) = "=R[-1]C"
        .Copy
        .PasteSpecial -4163
      End With
    End If
  Next

  Application.CutCopyMode = False
End Sub

No pasta, rigatoni, farfalle, tagliatelle or spaghetti.