Is there any way that 'filling' can be restricted to values only and not formatting, perhaps through VBA?

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
Hello,

I know that it is possible to right-click and drag and then select 'fill without formatting', but is there any formatting changes can be disabled for dragging/filling, perhaps through a VBA code? :biggrin:

Thanks for any input!
 
I just tried Selecting F11:F12 / copy / paste to M27 ... it worked fine

As far as the rest of your CTRL+Z questions ... There is no 'Undo' ie. CTRL+Z when it comes to VBA code. If VBA code made a change, you can't CTRL+Z to undo it.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Regarding "undo", they're not from the VBA code. It's the other operations I did like deleting or editing in the monitored ranges, and then I hit Control+Z

And not sure why, but I keep getting the error only when I paste into M17:M36. Could it be because my sheet is locked?
 
Upvote 0
Regarding "undo", they're not from the VBA code. It's the other operations I did like deleting or editing in the monitored ranges, and then I hit Control+Z

If you did that stuff on the sheet that is monitored, the vba code is involved in either allowing a change to happen, or modifying how it happens, if the vba code gets involved in the normal process, you can't CTRL+Z it.

And not sure why, but I keep getting the error only when I paste into M17:M36. Could it be because my sheet is locked?

Try it without sheet locked and see.
 
Upvote 0
Actually, when I tried with unlocked sheet, the first error didn't occur anymore, so I could easily paste into M17:M36

But the undo error still comes up. Can you try this: for example, write something in E5 and drag it down a few cells. Then select those cells and hit delete. And then hit Control+Z. For me it says: runtime error 9, script out of range.
 
Upvote 0
Ok, I think I may have made a deal with the devil, but I have come up with some code that should 'restore' the CTRL+Z options you mentioned that were failing.

Replace the old code with the following code & let me know your results, any other issues discovered, etc:

VBA Code:
Dim RangeSelected           As Variant
Dim PreviousRangeSelected   As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
    PreviousRangeSelected = RangeSelected                                                       ' Save the previously selected range
    RangeSelected = Target.Address(0, 0)                                                        ' Save the RangeSelected
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Intersect(Target, Range("E3:E42")) Is Nothing And _
            Intersect(Target, Range("F3:F42")) Is Nothing And _
            Intersect(Target, Range("G3:G42")) Is Nothing And _
            Intersect(Target, Range("M17:M36")) Is Nothing Then Exit Sub                        ' If change is not in our monitored range, Exit Sub
'
'-----------------------------------------------------------------------------------------------
'
    Dim AddressCharacter        As Long, EndAddressRow              As Long
    Dim SelectionRow            As Long
    Dim ActiveColumnLetter      As String, LastMergedColumn         As String
    Dim EndDragColumnLetter     As String, StartDragColumnLetter    As String
    Dim SelectionAddressArray   As Variant
'
'-----------------------------------------------------------------------------------------------
'
    With Selection
        If .Count = 1 And Application.CutCopyMode = False Then                                  '   If only 1 cell has been changed by user and
'                                                                                               '           not Cut/Copy then ...
            Exit Sub                                                                            '       allow the change & Exit the Sub
        ElseIf .Count = 1 And Application.CutCopyMode = xlCopy Then                             '   ElseIf only 1 cell has been changed by user and
'                                                                                               '       Copy then ...
            With Application
                .EnableEvents = False                                                           '           Turn Events off to prevent potential code loop
                .Undo                                                                           '           Undo the change that was made by the user
'
                Target.PasteSpecial Paste:=xlPasteValues                                        '           Do the Copy/paste as pastespecial PasteValues
                .CutCopyMode = False                                                            '           Clear the clipboard & 'Marching Ants'
                .EnableEvents = True                                                            '           Turn Events back on
                Exit Sub                                                                        '           Exit the sub
            End With
        End If
'
'-----------------------------------------------------------------------------------------------
'
        If .Count > 1 Then                                                                      '   If  more than one cell is being changed then
            With Application
                On Error Resume Next                                                            '           If error occurs, ignore it & exit sub
                If .CommandBars("Standard").Controls("&Undo").List(1) = "Clear" Then Exit Sub   '           Allow multiple deletetions via the delete key
                On Error GoTo 0                                                                 '           Return error handling back to Excel
'
                If .CommandBars("Standard").Controls("&Undo").List(1) = "Paste" Or _
                        .CommandBars("Standard").Controls("&Undo").List(1) = "Paste Special" And _
                        .CutCopyMode = xlCopy Then                                              '           If Copy/paste detected then ...
'
                    .EnableEvents = False                                                       '               Turn Events off to prevent potential code loop
'
                    LastMergedColumn = Split(Selection.Address, "$")(3)                         '               Get LastMergedColumn letter
'
                    .Undo                                                                       '               Undo the change that was made by the user
'
                    If ActiveCell.MergeCells Then                                               '               If ActiveCell is merged then ...
'
' Copy/Paste to Merged cell range
'
                        EndAddressRow = Split(Selection.Address, "$")(4)                        '               Get the ending address row number
'
                        ActiveColumnLetter = Split(Cells(1, ActiveCell.Column).Address, "$")(1) '               Get ActiveColumnLetter
'
                        With Range(ActiveColumnLetter & Target.Row & ":" & _
                                ActiveColumnLetter & EndAddressRow)
                            If .MergeCells Then .UnMerge                                        '                   Unmerge the range
                        End With
'
                        Range(PreviousRangeSelected).Copy                                       '               Copy the initial selected range
                        Range(ActiveColumnLetter & Target.Row & ":" & ActiveColumnLetter & _
                                EndAddressRow).PasteSpecial Paste:=xlPasteValues                '               Paste as pastespecial PasteValues
'
                        For MergeCellCounter = Target.Row To EndAddressRow                      '               Loop through the rows of the target
                            Range(ActiveColumnLetter & MergeCellCounter & ":" & _
                                    LastMergedColumn & MergeCellCounter).Merge                  '                   Merge the Row columns
                        Next                                                                    '               Loop back
'
                        .EnableEvents = True                                                    '               Turn Events back on
                        Exit Sub                                                                '               Exit Sub
                    Else                                                                        '           Else ...
'
' Copy/Paste to UnMerged cell range
'
                        Target.PasteSpecial Paste:=xlPasteValues                                '               Do the Copy/paste as pastespecial PasteValues
                        .CutCopyMode = False                                                    '               Clear the clipboard & 'Marching Ants'
                        .EnableEvents = True                                                    '               Turn Events back on
                        Exit Sub                                                                '               Exit Sub
                    End If
                End If
'
'-----------------------------------------------------------------------------------------------
'
                If ActiveCell.MergeCells And .CommandBars("Standard").Controls("&Undo").List(1) _
                        <> "Auto Fill" And .CommandBars("Standard").Controls("&Undo").List(1) _
                        <> "Paste" Then                                                         '       If Merged cell found & we are not dragging
'                                                                                               '               or copying a cell then ...
' Standard write to single merged cell
'
                    EndAddressRow = ActiveCell.Row                                              '           Save EndAddressRow
'
                    If EndAddressRow - Target.Row = 1 Then Exit Sub                             '           Allow a single Merged cell change
'
                ElseIf ActiveCell.MergeCells And .CommandBars("Standard").Controls("&Undo").List(1) _
                        = "Auto Fill" Or ActiveCell.MergeCells And _
                        .CommandBars("Standard").Controls("&Undo").List(1) = "Paste" Then       '       Else if Dragging or pasting to merged cell then ...
'
' Dragging Merged Cell or pasting to merged cell
'
                    .EnableEvents = False                                                       '           Turn Events off to prevent potential code loop
'
                    SelectionAddressArray = Split(Selection.Address(0, 0), ":")                 '           Separate Address Range into
'                                                                                               '               SelectionAddressArray
                    .Undo                                                                       '           Undo the change that was made by the user
'
                    Range(SelectionAddressArray(0) & ":" & _
                            SelectionAddressArray(1)).FormulaR1C1 = Selection.Cells(1)          '           Make the user changes without affecting format
                    .EnableEvents = True                                                        '           Turn Events back on
                    Exit Sub                                                                    '           Exit the sub
                End If
'
'-----------------------------------------------------------------------------------------------
'
                SelectionRow = Selection.Row                                                    '           Save the Selection.Row into SelectionRow...24
'
                EndDragColumnLetter = Split(Selection.Address, "$")(3)                          '           Get EndDragColumnLetter
                StartDragColumnLetter = Split(Cells(1, ActiveCell.Column).Address, "$")(1)      '           Get StartDragColumnLetter
'
                .EnableEvents = False                                                           '           Turn Events off to prevent potential code loop
'
                SelectionAddressArray = Split(Selection.Address(0, 0), ":")                     '           Separate Address Range into
'                                                                                               '               SelectionAddressArray
                .Undo                                                                           '           Undo the change that was made by the user
'
                If SelectionRow = ActiveCell.Row Then                                           '           If Dragging down then ...
                    Range(SelectionAddressArray(0) & ":" & EndDragColumnLetter & _
                            ActiveCell.Row).AutoFill Destination:=Range(SelectionAddressArray(0) & _
                            ":" & SelectionAddressArray(1)), Type:=xlFillValues                 '               Make the user changes without affecting format
                Else                                                                            '           Else ...
                    Range(StartDragColumnLetter & ActiveCell.Row & ":" & EndDragColumnLetter & _
                            ActiveCell.Row).AutoFill Destination:=Range(SelectionAddressArray(1) & _
                            ":" & SelectionAddressArray(0)), Type:=xlFillValues                 '               Make the user changes without affecting format
                End If
'
                .EnableEvents = True                                                            '           Turn Events back on
            End With
        End If
    End With
End Sub
 
Upvote 0
Solution
Wow, this is superb. I did all my tests on unprotected sheet, and your code came out victorious. Thanks much!!! 🤗 You are absolute number one 🙃

The only tiny thing was with the sheet protected, where pasting into M17:M16 didn't work, as we already knew it, so I was gonna ask your opinion:

One option is that I could leave the sheet unprotected, but then there is risk that the students would mess up with the contents, formulas, formatting, etc (you know how students can be 😅).

But I was thinking there is possibly another option, which is to momentarily unprotect a sheet through VBA code and then after pasting is done, immediately protect it back through VBA. I was reading this link: How to PROTECT and UNPROTECT a Sheet using VBA in Excel

Do you think this or something similar to this would be feasible (i.e. adding a couple of lines of code before and after the merging section of your code)? If it is feasible, and if it's not too much headache for you, do you mind doing it? I feel bad asking for more 🙂 so only do it if you feel great about it.

Thank you so much again for all your hard work and assistance 🤗
 
Upvote 0
I think if you are satisfied with the code thus far, you should mark the appropriate post as the solution and then move on to another thread with any more questions you may encounter.

This thread is nearly 100 posts long, has several solutions within it, but they are all trampling over themselves because you keep asking for more.
 
Upvote 0
Ok, thanks again. Just wanted to make sure you're ok if I ask for this last one enhancement 🙂. I will make a new post a with a more specific title and mention it's related to this thread.

Also I just marked your post #95 as solution. I think this will be very useful for many people because I saw many online threads were people were looking for exactly this type of code you wrote. You're a genius 🤗
 
Upvote 0
I'm typing it in as we speak. Should be ready in a few minutes. Thanks much for checking on me! 🤗
 
Upvote 0

Forum statistics

Threads
1,223,956
Messages
6,175,613
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top