如何仅显示具有特定值的列

Okay, I am completely new at this, so sorry for insulting anyone with what I suspect is a simple question. However, I have searched and attempted things for days and can't crack the nut - I just can't seem to get something to do all of the things I want.

好吧,我是全新的,所以很抱歉侮辱任何人我怀疑是一个简单的问题。然而,我已经搜索并尝试了好几天,并且无法破解坚果 - 我似乎无法完成所有我需要的事情。

Here goes: I have a worksheet with values that change weekly. The number of rows and columns change as well. However, columns A, B, and C will always have date, name and location data and therefore must be preserved. The values in the columns from D onward include only the numbers 0, 1, 2, or 3.

这里是:我有一个工作表,其值每周更改。行数和列数也会发生变化。但是,列A,B和C将始终具有日期,名称和位置数据,因此必须保留。从D开始的列中的值仅包括数字0,1,2或3。

I need to copy the columns to a second worksheet and then delete all of the columns from D onward that do not have a 2 or a 3 in them. In other words, I need to always keep columns A, B, and C, and also keep any column (and all of its data) if a 2 or 3 shows up anywhere in the column.

我需要将列复制到第二个工作表,然后从D向前删除所有没有2或3的列。换句话说,我需要始终保留列A,B和C,并且如果列中的任何位置显示2或3,则还要保留任何列(及其所有数据)。

Alternately, I bet it would be quicker to cherry pick the first three columns, as well as any other columns that have a 2 or 3 in them, and then paste them to the second worksheet. However, I've read about using Union, which seems like the way to go, but it's way over my head.

或者,我敢打赌,选择前三列,以及其中包含2或3列的任何其他列,然后将它们粘贴到第二个工作表上会更快。但是,我已经阅读过关于使用Union的内容,这似乎是要走的路,但它已经超出了我的想象。

Thanks in advance for any solutions.

提前感谢任何解决方案。

1 个解决方案

#1

I do not see the relevance of Union so I hope I have not misunderstood your requirement.

我没有看到联盟的相关性,所以我希望我没有误解你的要求。

The first task is to determine the last row and column. There are a variety of techniques for finding the last row or column; none of which work in every situation. I believe SpecialCells is the most suitable in this case.

第一项任务是确定最后一行和一列。查找最后一行或列的方法有很多种;这些都不适用于所有情况。我相信SpecialCells在这种情况下是最合适的。

When I am unsure how to achieve some objective, I break it into little tasks, code task 1 and use Debug.Print to output diagnostic information to the Immediate Window. When I have got task 1 working, I add the code for task 2 together with new diagnostic information. So my first macro, Demo1 just outputs the last row and column. Try placing values to the left or below any existing values to see what the macro outputs.

当我不确定如何实现某些目标时,我将其分解为小任务,代码任务1并使用Debug.Print将诊断信息输出到立即窗口。当我让任务1工作时,我将任务2的代码与新的诊断信息一起添加。所以我的第一个宏Demo1只输出最后一行和一列。尝试将值放在任何现有值的左侧或下方,以查看宏输出的内容。

Note: I say little about the statements I am using. In general it is easy to look up a statement once you know it exists. Come back with questions if necessary but please try your own investigation first.

注意:我对我使用的陈述几乎没有说明。通常,一旦您知道它存在,就很容易查找语句。如有必要,请回答问题,但请先尝试自己的调查。

Option Explicit
Sub Demo1()

  Dim ColLast As Long
  Dim RowLast As Long

  ' Replace "Source" with the name of your worksheet
  With Worksheets("Source")

    ColLast = Cells.SpecialCells(xlCellTypeLastCell).Column
    RowLast = Cells.SpecialCells(xlCellTypeLastCell).Row

  End With

  Debug.Print "Last column " & ColLast
  Debug.Print "Last row " & RowLast
  ' Note Cells(RowLast, ColLast) does not have to contain a value.

End Sub

The next task is to identify the columns to delete. I use the worksheet function CountIf to count the number of 2s and 3s in each column starting from column 4 which is column "D".

下一个任务是确定要删除的列。我使用工作表函数CountIf来计算从第4列(列为“D”)开始的每列中的2和3的数量。

Sub Demo2()

  Dim ColCrnt As Long
  Dim ColLast As Long
  Dim Rng As Range
  Dim RowLast As Long

  With Worksheets("Source")

    ColLast = Cells.SpecialCells(xlCellTypeLastCell).Column
    RowLast = Cells.SpecialCells(xlCellTypeLastCell).Row

    For ColCrnt = 4 To ColLast
      Set Rng = .Range(.Cells(1, ColCrnt), .Cells(RowLast, ColCrnt))
      Debug.Print ColCrnt;
      Debug.Print "  Num 2s=" & WorksheetFunction.CountIf(Rng, 2);
      Debug.Print "  Num 3s=" & WorksheetFunction.CountIf(Rng, 3)
    Next

  End With

End Sub

The final task is to delete the columns without 2s and 3s. For Demo2 I used a For-Loop. The trouble with a For-Loop is you cannot change the End Value within the loop and we will need to do that as we delete columns. So for Demo3, I have to use a Do-Loop.

最后的任务是删除没有2和3的列。对于Demo2,我使用了For-Loop。 For-Loop的问题是你无法在循环中更改End Value,我们需要在删除列时执行此操作。所以对于Demo3,我必须使用Do-Loop。

Sub Demo3()

  Dim ColCrnt As Long
  Dim ColLast As Long
  Dim Rng As Range
  Dim RowLast As Long

  With Worksheets("Source")

    ColLast = Cells.SpecialCells(xlCellTypeLastCell).Column
    RowLast = Cells.SpecialCells(xlCellTypeLastCell).Row

    ColCrnt = 4
    Do While ColCrnt <= ColLast
      Set Rng = .Range(.Cells(1, ColCrnt), .Cells(RowLast, ColCrnt))
      If WorksheetFunction.CountIf(Rng, 2) + _
         WorksheetFunction.CountIf(Rng, 3) > 0 Then
        ' This column contains a 2 or a 3.  Do not delete column.
        ' Advance to next column
        ColCrnt = ColCrnt + 1
      Else
        ' This column does not contain a 2 or 3.  Delete column.
        .Columns(ColCrnt).EntireColumn.Delete
        ' Reduce ColLast to allow for deletion.
        ColLast = ColLast - 1
      End If
    Loop

  End With

End Sub

Hope the above helps.

希望以上有所帮助。

#1