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!
 
The paste special (V) is the issue. I just tested it.

Don't do that!

Do a normal paste (P).
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sure. In that case, you can only take care of the issues in item A. I'll tell students to not use paste special.
 
Upvote 0
As I said, You don't need paste special, for two reasons, 1) it won't work for the 'Monitored' ranges, 2) The code for the 'monitored' ranges handles the 'paste special' for you. You don't need to worry about pasting just the values into the 'monitored' ranges, the code takes care of that for you.

Feel free to use paste special in the non 'monotored' ranges. That shouldn't be affected.
 
Upvote 0
Yes, got it.

In post 82, I was saying that regular control V paste still has issues, that I hope you are able to solve. Those are under item A in post 67.
 
Upvote 0
Can you explain that scenario? I already mentioned CTRL+C/CTRL+V worked fine for me for outside the 'monitored' ranges into the 'monitored' ranges.
 
Upvote 0
Here are the scenarios when pasting into the M17:M36:

1) Pasting one or more (vertically selected) cells from anywhere in E3:G42 into M17:M36. (for example on E5, I hit control+C and then click M20 and hit Control+V)
2) Pasting one or more cells from anywhere in M17:M36 into M17:M36. (for example on M20, I hit control+C and then click M22 and hit Control+V)
3) Pasting one or more (vertically selected) cells from outside monitored ranges into M17:M36. (for example on M5, I hit control+C and then click M25 and hit Control+V)

In all these instances, it shoots to:

If .MergeCells Then .UnMerge ' Unmerge the range

where Unmerge is "highlighted"
 
Upvote 0
Again, I do not have issue with outside 'monitored' range into 'monitored' range, ie. #3 that you just mentioned.

I will start looking into the foreseen 'flaws', that I previously mentioned ... copying from within 'monitored' ranges to within 'monitored' ranges.
 
Upvote 0
I see. Did you try pasting from outside monitored ranges into M17:M36, because that's the part of the monitored range where the error occurs? You may have pasted into E3:G42 which works perfectly fine.

And thanks for checking the scenarios "from within 'monitored' ranges to within 'monitored' ranges"
 
Upvote 0
Ok I have made some more edits to the code.

I looked into using the Paste special method that you said you wanted to use but you were getting error. I have added that option into the code so it should work for you now, whether you choose paste or paste special in the non merged 'monitored' ranges. Be advised Excel will not allow you to 'PasteSpecial' into merged cells. If you attempt to do that, Excel intercepts such attempts immediately, prior to any vba code being run.

I also shortened the code a bit.

I also fixed the copying from within 'monitored' ranges to 'monitored' ranges.

Probably some other stuff, I forget right now.

Anyway, test out the following and let me know how it goes, and any other issues you still have or encounter:

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
                If .CommandBars("Standard").Controls("&Undo").List(1) <> "Auto Fill" And _
                        .WorksheetFunction.CountA(Target) = 0 Then Exit Sub                     '           Allow deletetions via the delete key
'
''                If .CommandBars("Standard").Controls("&Undo").List(1) = "Paste" And _
                        .CutCopyMode = xlCopy Then                                              '           If Copy/paste detected then ...
                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
Wow, this is amazing and better than ever! Thank you so much for the extra work to take care of 'paste special' as well 🤗 As far as I could see, 'paste special' is working fine.

So far, I only found two bugs below, and I'm gonna use the shorthand OMR for "outside monitored ranges":

1) (same as yesterday's code) regular pasting of one or more vertically selected cells from either E3:G42, M17:M36, or OMR into M17:M36

this shoots to:

If .MergeCells Then .UnMerge '

where UnMerge is highlightd


2) (a potentially new bug, or maybe I didn't test the below situations before) certain undo (Control+Z) actions:
a) deleting more than one vertically selected cells in E3:G42, and then hitting Control+Z
b) deleting one or more cells in M17:M36, and then hitting Control+Z
c) editing one cell in M17:M36, and then hitting Control+Z

these shoot to:

If .CommandBars("Standard").Controls("&Undo").List(1) <> "Auto Fill" And _
.WorksheetFunction.CountA(Target) = 0 Then Exit Sub '

d) deleting more than one horizontally selected cells in E3:G42, and then hitting Control+Z

this shoots to:

Range(SelectionAddressArray(0) & ":" & EndDragColumnLetter & _
ActiveCell.Row).AutoFill Destination:=Range(SelectionAddressArray(0) & _
":" & SelectionAddressArray(1)), Type:=xlFillValues '
 
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