Sort Warning - "Microsoft Excel found data next to your selection."

Question

Hello all:

I have a spreadsheet to track weekly and monthly grocery store sales.

I would like to find a way around a particular quirk in Excel.

I would like to just sort a particular subset of cells in a column from A to Z (see the first screenshot I have attached).

However, almost every time, a pop-up appears asking me if I would like to "Expand the selection" to include adjacent data (see the second screenshot I have attached).

As you can probably guess, regardless of why the feature for this message was implemented, I *absolutely* DO NOT NEED it to be active in this particular spreadsheet.

I would like to find a way to disable the pop-up message from appearing or change the default selection in the pop-up to be "Continue with the current selection."

The only maybe-related option I have found is in Excel Options on the Formulas tab.  A couple troubleshooting boards have posts that indicate people who received this pop-up have been able to disable it by unchecking the "Formulas which omit cells in a region" option on the Formulas tab.  However, I have unchecked it and restarted both Excel and my computer to no avail.

I would like to see if any fellow Excel users have found a workaround or a way to disable this message.

Thank you in advance for your assistance!

Answer 1

re: do it my way

I can't recall anything that suppresses that message.
It would be nice.

You can do your own sort, by adding the vba code below to a

standard module in the store sales workbook.

Add a button to the quick access toolbar to run the code.
The code only sorts one column and only sorts ascending.
Select the data and click the button.
'---
Sub NLtL_BubbleSort()

'Nothing Left to Lose - March 2021

Dim First As Long

Dim Last As Long

Dim rng As Excel.Range

Dim i As Long

Dim j As Long

Dim Temp As Variant

Dim arrList As Variant

Set rng = Excel.Selection

If rng.Columns.Count > 1 Then

VBA.MsgBox "   Can only sort one column.", vbInformation, "Limited Sort"

Else

arrList = rng.Value

First = LBound(arrList, 1)

Last = UBound(arrList, 1)

For i = First To (Last - 1)

    For j = Last To (i + 1) Step -1

        If arrList(i, 1) > arrList(j, 1) Then

            Temp = arrList(j, 1)

            arrList(j, 1) = arrList(i, 1)

            arrList(i, 1) = Temp

        End If

    Next j

Next i

rng.Value = arrList

End If

End Sub

'---

NLtL https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O Free: Add_Table of Contents, Calculate Payments, Custom_Functions, Professional_Compare, USA Lottery Numbers

Answer 2

Hello YehKahnYiin

Am an independent advisor and Microsoft office user like You.

I the options you unchecked the "Formulas which omit cells in a region", you also need to uncheck the "Enable Background Error Checking"

As you do that many error checking facilities will be disabled because these are system wide options. You might not be able to see error that occur when for instance you do a formula wrongly. This might end up working against you. The feature actually assists to some extent though for your preference, disable in this way and see how it works for you.

Do let me know if it works for you.

All the best

Answer 3

Hello, Pakrashio

I'm an excel user who, like the OP, uses this sort feature so often for just the one selection, that the message becomes burdensome. I've been using this for years. Never at any point have I needed to 'expand the selection' - I can simply do that myself if I ever need to.

Unfortunately, your suggestion didn't work. Actually, I also went ahead and unticked all error checking rules, including background error checking and it still appears.

Is there any rules elsewhere I need to uncheck? 

Thank you.