更快更有效地删除行 [英] faster and efficient way of deleting rows

问题描述

我正在尝试做什么。

我有两个工作表dashboard和temp calc我想删除

仪表板 - 如果列号为15的活动

$ b，则删除行
$ b

如果列号为10&＃8; E& D，ESG，PLM SER，VPD，PLM Prod。

Temp calc =如果列号6为空白，则删除行

如果列号为3n1
，则删除行，其中n1和n2是从范围n1和n2）在仪表板中。

我尝试了什么。

使用一个过滤器

使用一个for循环

数组（我无法使用一个数组

我的问题

很慢，我的数据是大约1,68,000（每周增长）。我正在寻找替代品我有什么特色天。基本上这将是快速的。

我已经尝试过的代码。
以下代码可以工作，但它需要6- 10分钟取决于数据

 工作表（Dashboard）。激活
范围（A4）。 
 lastrow = Cells（Rows.Count，1）.End（xlUp）.Row 
对于x = lastrow到4 Step -1 
如果Cells（x，15）.Value& ; 活动或（细胞（x，10）.ValueE& D和细胞（x，10）.ValueESG_ 
和细胞（x，10） .ValuePLM SER和单元格（x，10）.ValueVPD和单元格（x，10）.ValuePLM PROD）然后
行（x）。删除
结束如果
下一个x 
  

以下代码使用自动过滤方法，问题是过滤后不在我的比较范围内的数据（即如果我的n1 = 1月1日和n 2 = 2013年1月30日。过滤器仍然会留下不在n1和n2中的数据范围。

 设置ws = ThisWorkbook.Worksheets（Temp Calc）
 
 
 
'~~>开始日期和结束日期
表（仪表板）选择
 N1 =范围（n1）值
 N2 =范围（n2 ）.Value 
 Sheets（Temp Calc）。选择
 
与ws 
 
'~~>删除任何过滤器
 .AutoFilterMode = False 
 
'~~>获取最后一行
 lRow = .Range（A& .Rows.Count）.End（xlUp）.Row 
 
'~~>确定您的数据范围
设置FltrRng = .Range（A1：F& lRow）
 
'~~>根据您的条件过滤数据
使用FltrRng 
'~~>空白上的第一个过滤器
 .AutoFilter字段：= 6，Criteria1：==
 
 Application.ScreenUpdating = False 
 Application.Calculation = xlCalculationManual 
'~~ >删除过滤的空白行
 .Offset（1，0）.SpecialCells（xlCellTypeVisible）.EntireRow.Delete 
 
 ws.ShowAllData 
 
'~~>开始日期的下一个过滤器
 .AutoFilter字段：= 3，条件1：=< &安培; N1，Operator：= xlAnd 
'~~>最后过滤结束日期
 .AutoFilter字段：= 4，Criteria1：=> &安培; N2，运算符：= xlAnd 
 
'~~>过滤第6栏为CNF 
'.AutoFilter字段：= 6，条件1：=CNF
 
'~~>删除已过滤的行
 .Offset（1，0）.SpecialCells（xlCellTypeVisible）.EntireRow.Delete 
 End with 
 
 Application.ScreenUpdating = True 
 Application.Calculation = xlCalculationAutomatic 
 
'~~>删除任何过滤器
 .AutoFilterMode = False 
结束
  

问题不够
非常感谢任何可以加快我想要做的替代方案。

解决方案

p>

  Sub DeleteRows（）
 
 Dim x As Long 
 Dim Rng As Range 
 Dim lastRow As Long 
 
 Application.ScreenUpdating = False 
 Application.Calculation = xlCalculationManual 
 
带表格（Dashboard）
 lastRow = .Cells .Rows.Count，1）.End（xlUp）.Row 
设置Rng = .Range（A1：A& lastRow）
 
 
对于x = Rng。 Rows.Count to 1 Step -1 
如果.Cells（x，15）.Value<> 活动或（.Cells（x，10）.ValueE& D和.Cells（x，10）.Value"ESG_ 
 And .Cells（x ，10）.ValuePLM SER和.Cell（x，10）.ValueVPD和.Cells（x，10）.ValuePLM PROD）然后
 Rng.Rows（x）.Delete 
结束如果
下一个
 
结束
 
 Application.ScreenUpdating = True 
 Application.Calculation = xlCalculationAutomatic 
 
 End Sub 
  

what i am trying to do.

I have two worksheets "dashboard" and "temp calc".I am trying to delete rows based on two different conditions in each worksheet.

Dashboard- delete rows if column number 15 <> active

delete rows if column number 10 <> E&D,ESG,PLM SER,VPD,PLM Prod.

Temp calc = Delete rows if column number 6 is blank

delete rows if column number 3n1 where n1 and n2 are dates taken from range("n1" and "n2") in dashboard.

What I have tried.

1. using a for loop
2. using a filter
3. arrays(I am unable to actually do this using an array

My Problem

these methods are very slow and my data is around 1,68,000(grows on a weekly basis).So I am looking for alternatives to what I have tried. Basically something which will do this fast.

my codes I have tried. the below code works but it takes upto 6-10 minutes depending on the data

                    Worksheets("Dashboard").Activate
                    Range("A4").Select
                    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
                    For x = lastrow To 4 Step -1
                     If Cells(x, 15).Value <> "Active" Or (Cells(x, 10).Value <> "E&D" And Cells(x, 10).Value <> "ESG" _
                     And Cells(x, 10).Value <> "PLM SER" And Cells(x, 10).Value <> "VPD" And Cells(x, 10).Value <> "PLM PROD") Then
                    Rows(x).Delete
                    End If
                    Next x

The below code uses the autofilter method.the problem is that data which is not in my compare range is left after filtering(i.e if my n1 =1st Jan and n2=30th jan 2013. the filter will still leave behind data that is not in the n1 and n2 range.

Set ws = ThisWorkbook.Worksheets("Temp Calc")



   '~~> Start Date and End Date
   Sheets("Dashboard").Select
N1 = Range("n1").Value
N2 = Range("n2").Value
Sheets("Temp Calc").Select

With ws

'~~> Remove any filters
.AutoFilterMode = False

'~~> Get the last row
lRow = .Range("A" & .Rows.Count).End(xlUp).Row

'~~> Identify your data range
Set FltrRng = .Range("A1:F" & lRow)

'~~> Filter the data as per your criteria
With FltrRng
'~~> First filter on blanks
.AutoFilter Field:=6, Criteria1:="="

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'~~> Delete the filtered blank rows
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

ws.ShowAllData

'~~> Next filter on Start Date
.AutoFilter Field:=3, Criteria1:="<" & N1, Operator:=xlAnd
'~~> Finally filter on End Date
.AutoFilter Field:=4, Criteria1:=">" & N2, Operator:=xlAnd

'~~> Filter on col 6 for CNF
'.AutoFilter Field:=6, Criteria1:="CNF"

'~~> Delete the filtered rows
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

'~~> Remove any filters
.AutoFilterMode = False
End With

Apologies in case the question is not adequate. Any alternatives which will speed up what I am trying to do is highly appreciated.

解决方案

Try below code

Sub DeleteRows()

    Dim x As Long
    Dim Rng As Range
    Dim lastRow As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With Sheets("Dashboard")
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set Rng = .Range("A1:A" & lastRow)


        For x = Rng.Rows.Count To 1 Step -1
            If .Cells(x, 15).Value <> "Active" Or (.Cells(x, 10).Value <> "E&D" And .Cells(x, 10).Value <> "ESG" _
             And .Cells(x, 10).Value <> "PLM SER" And .Cells(x, 10).Value <> "VPD" And .Cells(x, 10).Value <> "PLM PROD") Then
                Rng.Rows(x).Delete
            End If
        Next

    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

这篇关于更快更有效地删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！