excel 错误_Excel错误–选择太大

excel 错误

To fill blank cells, or delete rows with blanks cells, you can use Excel's Go To Special feature.

要填充空白单元格，或使用空白单元格删除行，可以使用Excel的转到特殊功能。

For example, in the worksheet shown below, you might want to fill in all the blanks in column B, by copying the value from the row above.

例如，在下面显示的工作表中，您可能希望通过复制上一行的值来填写B列中的所有空白。

There are instructions on the Contextures website to fill blank cells, by using Go To Special to select the blanks.

Contextures网站上有说明，可以使用“转到特殊位置”选择空白来填充空白单元格 。

You can do this manually, and there's sample code to make the job easier.

您可以手动执行此操作，并且提供了示例代码来简化工作。

选择太大错误 (Selection Is Too Large Error)

This technique works very well, unless you're trying to fill blank cells in a long list. In that case, you might see the error message, "Selection is too large."

除非您试图在长列表中填充空白单元格，否则此技术非常有效。 在这种情况下，您可能会看到错误消息“选择太大”。

This happens in Excel 2007, and earlier versions, because there is a limit of 8192 separate areas that the special cells feature can handle. (This problem has been fixed in Excel 2010.)

这在Excel 2007和早期版本中会发生，因为特殊单元格功能可以处理8192个单独的区域。 (此问题已在Excel 2010中修复。)

There are details on Ron de Bruin's website: SpecialCells Limit Problem.

在Ron de Bruin的网站上有详细信息：SpecialCells限制问题。

小批量工作 (Work in Smaller Chunks)

If you run into this error, you can work with smaller chunks of data instead.

如果遇到此错误，则可以处理较小的数据块。

• If you're making the changes manually, select a few thousand rows, instead of the full column.
  如果您要手动进行更改，请选择几千行而不是整列。
• If you're using a macro, you can loop through the cells in large chunks, e.g. 8000 rows, instead of trying to change the entire column.
  如果使用的是宏，则可以以较大的块(例如8000行)循环遍历单元格，而不必尝试更改整个列。

On the Contextures website, Fill Blank Cells Macro – Example 3 checks for the number of areas, using Ron's sample code, and uses a loop if necessary.

在Contextures网站上，“ 填充空白单元格宏–示例3”使用Ron的示例代码检查区域数，并在必要时使用循环。

The code is shown below, and it shows a message box if the range is over the special cells limit. You can remove that line -- it's just there for information.

代码如下所示，如果范围超出特殊单元格限制，则会显示一个消息框。 您可以删除该行-此处仅供参考。

Sub FillColBlanks()
'https://www.contextures.com/xlDataEntry02.html
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above
'2010-10-12 incorporated Ron de Bruin's test for special cells limit
'https://www.rondebruin.nl/specialcells.htm
Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long
Dim lCount As Long
On Error Resume Next
lRows = 2 'starting row
lLimit = 8000
Set wks = ActiveSheet
With wks
   col = ActiveCell.Column
     'try to reset the lastcell
   Set rng = .UsedRange
   LastRow = .Cells. _
     SpecialCells(xlCellTypeLastCell).Row
   Set rng = Nothing
    lCount = .Columns(col) _
            .SpecialCells(xlCellTypeBlanks) _
            .Areas(1).Cells.Count
    If lCount = 0 Then
        MsgBox "No blanks found in selected column"
        Exit Sub
    ElseIf lCount = .Columns(col).Cells.Count Then
		   'this line can be deleted
        MsgBox "Over the Special Cells Limit" 
        Do While lRows < LastRow
            Set rng = .Range(.Cells(lRows, col),  _
            .Cells(lRows + lLimit, col)) _
             .Cells.SpecialCells(xlCellTypeBlanks)
            rng.FormulaR1C1 = "=R[-1]C"
            lRows = lRows + lLimit
        Loop
    Else
        Set rng = .Range(.Cells(2, col),  _
            .Cells(LastRow, col)) _
            .Cells.SpecialCells(xlCellTypeBlanks)
        rng.FormulaR1C1 = "=R[-1]C"
    End If
   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With
End With
End Sub

翻译自: https://contexturesblog.com/archives/2010/10/18/excel-error-selection-is-too-large/

excel 错误