如果单元格在VBA / Excel中包含特定值，那么如何从范围中删除列 [英] How to delete column from range if cell contains specific value in VBA/Excel

问题描述

我正在尝试写一些VBA，它将检查一列单元格（行M到GD）中的单元格的值，如果单元格不包含YY删除列。

要检查的单元格始终位于第22行

我尝试过以下操作，但这很慢。 >

  w = 186 
 Do 
如果Worksheets（SOF）。Cells（22，w）.Formula = YY然后
w = w  -  1 
 Else 
工作表（SOF）。单元格（22，w）.EntireColumn.Delete 
如果
w = w -  1 
循环直到w <有没有人有任何建议，如何加快速度或更好的方式来解决这个问题？ 
 
 
 
谢谢
解决方案
 
 
 
有什么建议，如何加快速度或更好的方式来做这个问题？
 
 
 Yup有。 不要删除循环中的列 。使用 Union 方法。这是一个例子。我已经评论过代码，所以你不会有一个问题的理解。仍然如果你这样做只是简单地发回。
  Option Explicit 
 
 Sub Sample（）
 Dim ws As Worksheet 
 Dim i As Long 
 Dim delRange As Range 
 
'~~>设置为相关工作表
设置ws = ThisWorkbook.Sheets（SOF）
 
与ws 
'~~>循环相关栏
 For i = 13 To 186 
'~~>检查是否等于YY 
如果UCase（修剪（.Cells（22，i）.Value））=YY然后
'~~>存储范围以后删除
如果delRange不是，然后
设置delRange = .Columns（i）
 Else 
设置delRange = Union（delRange，.Columns（i））
结束如果
结束如果
下一个i 
结束
 
'~~>删除相关列一次
如果不是delRange是没有，然后delRange.Delete 
结束Sub 
  
这将在眨眼中执行，但如果需要，您可以将代码夹在 Application.ScreenUpdating = False 和 Application.ScreenUpdating = True  
 
I’m trying to write a bit of VBA which will check the value of a cell in a range of columns (rows M to GD), if the cell does not contain "YY" delete the column.

The cell to check is always in row 22

I’ve tried the following, but it’s seriously slow.
w = 186
Do
If Worksheets("SOF").Cells(22, w).Formula = "YY" Then
w = w - 1
Else
   Worksheets("SOF").Cells(22, w).EntireColumn.Delete
End If
w = w - 1
Loop Until w < 13
Does anyone have any suggestions on how to speed this up or a better way to do this problem?

Thanks
解决方案

  
Does anyone have any suggestions on how to speed this up or a better way to do this problem?
Yup there is. Do not delete the columns in a loop. Use the Union method. Here is an example. I have commented the code so you will not have a problem understanding it. Still if you do then simply post back.
Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim i As Long
    Dim delRange As Range

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("SOF")

    With ws
        '~~> Loop through relevant columns
        For i = 13 To 186
            '~~> Check if the value is equal to YY
            If UCase(Trim(.Cells(22, i).Value)) = "YY" Then
                '~~> Store the Range to delete later
                If delRange Is Nothing Then
                    Set delRange = .Columns(i)
                Else
                    Set delRange = Union(delRange, .Columns(i))
                End If
            End If
        Next i
    End With

    '~~> Delete the relevant columns in one go
    If Not delRange Is Nothing Then delRange.Delete
End Sub
This would execute in a blink of an eye but if you want, you can sandwich the code between Application.ScreenUpdating = False and Application.ScreenUpdating = True

                        
这篇关于如果单元格在VBA / Excel中包含特定值，那么如何从范围中删除列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！