问题描述
我正在尝试删除电子表格中一列中的所有单元格 =0,并召唤"不在该列顶部的值.
I am trying to delete all cells =0 in a column in my spreadsheet and "summon" the values which don't to the top of the column.
我目前正在使用
Dim row_index As Integer
Dim col_index As Integer
row_index = 7
col_index = 16
Application.ScreenUpdating = False 'turns off screen updates
While Cells(row_index, col_index) <> ""
If Cells(row_index, col_index) = 0 Then
Cells(row_index, col_index).Delete
Else
row_index = row_index + 1
End If
Wend
Application.ScreenUpdating = True 'turns screen updates back on
但即使关闭屏幕更新,它也非常慢,因为数据集在 500-3500 点之间.是否有更好的方法来执行此操作或任何其他提示来加快速度?
But even with screen updating off it is very slow as the datasets are between 500-3500 points.Is there a better way to do this or any other tips to speed it up?
谢谢
网络上有一些解决方案,但它们似乎都涉及消隐单元格或删除行.我只想删除单元格,然后将单元格上移.
there are a few solutions on the web but they all seem to involve blanking cells or deleting rows. I only want to delete cells and then shift cells up.
推荐答案
在循环中删除单元格真的很慢.您可以做的是确定要在循环中删除的单元格,然后在循环后一次性删除它们.试试这个.
Deleting cells in a loop can really be very slow. What you could do is identify the cells that you want to delete in a loop and then delete them in one go after the loop. Try this.
Option Explicit
Sub Sample()
Dim row_index As Long, lRow As Long, i As Long
Dim ws As Worksheet
Dim delRange As Range
'~~> Change this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
row_index = 7
Application.ScreenUpdating = False
With ws
lRow = .Range("P" & .Rows.Count).End(xlUp).Row
For i = row_index To lRow
If .Range("P" & i).Value <> "" And .Range("P" & i).Value = 0 Then
If delRange Is Nothing Then
Set delRange = .Range("P" & i)
Else
Set delRange = Union(delRange, .Range("P" & i))
End If
End If
Next
End With
If Not delRange Is Nothing Then delRange.Delete shift:=xlUp
Application.ScreenUpdating = True
End Sub
这篇关于当行 = 0 时删除 Excel 列中的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!