VBA只排序文本，忽略具有公式的单元格 [英] VBA only sort text, disregard cells with formulas

问题描述

我有一个非常大的页面〜150列，其中大部分包含公式。当我想对不输入公式的单元格输入的数据进行排序时，会弄乱整个工作表。 - 输入单元格不在一起

目前，我在VBA中的解决方案是将单元格复制到另一个（隐藏）表单，排序并将其全部放回。我觉得这对于一个看似简单的任务来说太多了。有更聪明的方法解决这个问题吗？

编辑

qua @varocarbas我已经尝试了以下代码：

  Private Sub sortInputButton_Click（）
'这个子对inpur进行排序，而不会弄乱引用[希望] 
 Dim rowCount As Integer 
 Dim colCount As Integer 
 
 rowCount = countItems 
 colCount = 180 
 
 Dim inputRange As范围
设置inputRange =表（输入）。范围（A3）
设置inputRange = inputRange.Resize（rowCount，colCount）
 
 Dim targetRange As Range 
 
 On Error Resume Next'如果range没有什么，会抛出一个错误 - 我们不想要
设置targetRange = inputRange.SpecialCells（xlCellTypeConstants）
 On Error GoTo 0 
 
如果targetRange.Cells.count> 0然后
表单（输入）。选择
 targetRange.Select 
调用targetRange.Sort（表格（Input）。范围（A3），xlAscending）
结束如果
 
 End Sub 
  

但是这给我错误您选择的命令无法执行多个选择。选择一个范围，然后再次单击该命令。

解决方案

您可以使用 SpecialCells ：

  Dim targetRange As Range 
 On Error Resume Next'如果没有在要求的条件下找到任何电池。 
设置targetRange = inputRange.SpecialCells（xlCellTypeConstants）
  

inputRange 包括所有单元格和 targetRange 只是不包含公式的元素。

更新：

  Dim targetRange As Range 
 On Error Resume Next'如果range无效，会抛出一个错误 - 我们不想要
设置targetRange = inputRange.SpecialCells（xlCellTypeConstants）
调用targetRange.Sort（targetRange.Cells（1，1），xlAscending）
'targetRange.Sort targetRange.Cells（1，1 ），xlAscending  - >替代不需要Call
  

您不需要 On Error GoTo 0 ， targetRange.Cells.count （如果 targetRange 没有单元格， On Error Resume Next 会抓住它 - >这就是为什么我包括这一行; SpecialCells 有这个小的限制）。您不需要任何。选择您所包含的呼叫。而在 .Sort 中可能会引发错误是 Sheets（Input）。Range（A3）我已经替换为 targetRange （无论是什么）的第一个单元格。

I have a very large sheet with ~150 columns, most of which contains formulas. When i want to sort the data that i input to the cells that do not use formulas, it messes up the entire sheet. - The input cells are not together

Currently my solution in VBA would be to copy the cells to another (hidden) sheet, sort and put it all back. I just feel that this is way too much work for a seemingly simple task. Is there a smarter way to solve this?

EDIT

qua @varocarbas i have tried the following code:

Private Sub sortInputButton_Click()
    ' This sub sorts the inpur, without messing up the references [hopefully]
    Dim rowCount As Integer
    Dim colCount As Integer

    rowCount = countItems
    colCount = 180

    Dim inputRange As Range
    Set inputRange = Sheets("Input").Range("A3")
    Set inputRange = inputRange.Resize(rowCount, colCount)

    Dim targetRange As Range

    On Error Resume Next ' If range is nothing, throws an error - we don't want that
    Set targetRange = inputRange.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0

    If targetRange.Cells.count > 0 Then
        Sheets("Input").Select
        targetRange.Select
        Call targetRange.Sort(Sheets("Input").Range("A3"), xlAscending)
    End If

End Sub

But this give me the error the command you chose cannot be performed with multiple selections. Select a single range and click the command again.

解决方案

You can use SpecialCells:

Dim targetRange As Range
On Error Resume Next 'In case of not finding any cell under the requested conditions.
Set targetRange = inputRange.SpecialCells(xlCellTypeConstants)

inputRange includes all the cells and targetRange just the ones not including a formula.

UPDATE:

Dim targetRange As Range
On Error Resume Next ' If range is nothing, throws an error - we don't want that
Set targetRange = inputRange.SpecialCells(xlCellTypeConstants)
Call targetRange.Sort(targetRange.Cells(1, 1), xlAscending)
'targetRange.Sort targetRange.Cells(1, 1), xlAscending -> alternative not requiring "Call"

You don't need On Error GoTo 0, neither targetRange.Cells.count (if targetRange does not have cells, On Error Resume Next would catch it -> that's why I included this line; SpecialCells has this small limitation). You don't need any of the .Select calls you are including either. And what is presumably provoking the error in .Sort is Sheets("Input").Range("A3") which I have replaced with the first cell of targetRange (whatever it is).

这篇关于VBA只排序文本，忽略具有公式的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！