Excel VBA 删除行 [英] Excel VBA Delete Rows
问题描述
我正在尝试创建一个程序,该程序将根据用户在某行中放置的内容删除或创建行.例如,如果用户输入 3,但只有 2 行,那么它将插入 1 行.如果有 5 行,它会删除第 4 行和第 5 行.看起来它应该是一个简单的代码,但我最难的是让它真正删除/创建我想要的行.我的代码如下:
Sheets("Summary").Selectx = Cells(29, 3).Value我 = 7工作表(加权 I").选择直到单元格(i,1).值=总"我 = 我 + 1环形我 = 我 - 7如果我>x 然后dlt = i - x + 7对于 cnt = 7 到 dlt行(cnt).EntireRow.Deletecnt = cnt + 1下一个否则如果我<x 然后crt = x - i + 7对于 cnt = 7 到 dlt行(cnt).EntireRow.Insertcnt = cnt + 1下一个万一
这是删除行时的常见问题.想象一下,您正在 for 循环中一次移动一行并删除:
For cnt = 7 To dlt行(cnt).EntireRow.Deletecnt = cnt + 1下一个
您在第 7 行并删除了它.这会将您的所有行向上移动.第 8 行现在是第 7 行.然后您将 cnt
变量增加 1(到 8)并删除第 8 行.但是您错过了第 7 行,也就是第 8 行......这是疯狂的香蕉.>
相反,将您的 for 循环更改为向后工作:
对于 cnt = dlt 到 7 step -1行(cnt).EntireRow.Deletecnt = cnt - 1下一个
这样行移动就不会影响您的 cnt
.
I am trying to create a program that will delete or create rows based on what a user puts in a certain row. For example, if the user puts in 3, but there are only 2 rows, then it will insert 1 row. If there were 5 rows, it would delete rows 4 and 5. It seems like it should be an easy code, but I am having the hardest time having it actually delete/create the rows that I want it to. My code is as follows:
Sheets("Summary").Select
x = Cells(29, 3).Value
i = 7
Sheets("Weighted I").Select
Do Until Cells(i, 1).Value = "TOTAL"
i = i + 1
Loop
i = i - 7
If i > x Then
dlt = i - x + 7
For cnt = 7 To dlt
Rows(cnt).EntireRow.Delete
cnt = cnt + 1
Next
ElseIf i < x Then
crt = x - i + 7
For cnt = 7 To dlt
Rows(cnt).EntireRow.Insert
cnt = cnt + 1
Next
End If
This is a common problem when deleting rows. Imagine you are moving through your for loop one row at a time and deleting:
For cnt = 7 To dlt
Rows(cnt).EntireRow.Delete
cnt = cnt + 1
Next
You are on row 7 and you delete it. This shifts all of your rows up. Row 8 is now Row 7. You then increase your cnt
variable by 1 (to 8) and delete row 8. But you missed row 7, which was row 8... it's crazy bananas.
Instead, change your for loop to work backwards:
For cnt = dlt to 7 step -1
Rows(cnt).EntireRow.Delete
cnt = cnt - 1
Next
This way the row shifting doesn't affect your cnt
.
这篇关于Excel VBA 删除行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!