Paul, that is exactly it. Just trying to wrap my head around the change here.
Thanks again!
Paul, that is exactly it. Just trying to wrap my head around the change here.
Thanks again!
I used the file in your first post:
This is all you need for the first 4 columns:
For the first 4 columns in all sheets in the active workbook: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
NB. You should never use merged cells.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
Last edited by snb; 03-21-2022 at 03:47 AM.
@snb --
1. I like your more efficient way, but this below eliminates the .Areas loop
2.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 SubIMVHO, 'Never' is mostly correct, but I'd agree that 97% of the time merged cells only cause troubleNB. You should never use merged cells.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
@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
Last edited by Paul_Hossler; 03-22-2022 at 03:26 AM.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Here you go:
No pasta, rigatoni, farfalle, tagliatelle or spaghetti.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