VBA:如何删除行并根据条件保留一些行?
问题描述:
我一直在写这个宏,它有三个步骤。 首先是删除行,如果行是C列和二步骤之后空在那里与冠军有留在工作簿就像捐款,所有其他或课程费用行-Youth。 第三步步骤是通过在某些标题之后添加空格或空行来格式化该行。VBA:如何删除行并根据条件保留一些行?
这是我的代码,它似乎没有编译,我不知道如何防止行删除...请帮助。
Sub RemoveRowsAndFormat()
Dim WS As Worksheet
For Each WS In Sheets
WS.Activate
Dim n As Long
Dim nlast As Long
Dim rw As Range
Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows
nlast = rw.Count
For n = nlast To 9 Step -1
If (rw.Cells(n, 3).Value = "Contributions-All Other" Or rw.Cells(n, 3).Value = "Program Fees - Youth" Or rw.Cells(n, 3).Value = "Financial Assitance" Or rw.Cells(n, 3).Value = "Salaries & Wages" Or rw.Cells(n, 3).Value = "Payroll Taxes" Or rw.Cells(n, 3).Value = "Employee Benefits" Or rw.Cells(n, 3).Value = "Staff Training and Confer." Or rw.Cells(n, 3).Value = "Occupancy" Or rw.Cells(n, 3).Value = "Supplies" Or rw.Cells(n, 3).Value = "Telephone" Or rw.Cells(n, 3).Value = "Postage & Shipping" Or rw.Cells(n, 3).Value = "Promotion and Advertising" Or rw.Cells(n, 3).Value = "Bad Debt" Or rw.Cells(n, 3).Value = "Program Operating Expense" Or rw.Cells(n, 3).Value = "Program Operating Net") Then
rw.Rows(n).EntireRow.Insert
ElseIf (rw.Cells(n, 4).Value = "" And rw.Cells(n, 5).Value = "" And rw.Cells(n, 6).Value = "" And rw.Cells(n, 7).Value = "" And rw.Cells(n, 8).Value = "" And rw.Cells(n, 9).Value = "" And rw.Cells(n, 10).Value = "" And rw.Cells(n, 11).Value = "") Then
rw.Rows(n).Delete
End If
Next n
Next WS
End Sub
答
在你(编辑)问题的代码似乎是在做你想要什么,除了它是添加行上述你的标题,而不是下面它。这可以通过将rw.Rows(n).EntireRow.Insert
更改为rw.Rows(n + 1).EntireRow.Insert
来解决,但由于您定义的方式rw
,可能会导致问题(如果标题存在于最后一行)。
我重构了您的代码以使用Select Case
语句来替换您的(IMO)笨拙的If
语句,并在决定执行插入/删除操作的位置时引用工作表而不是仅某些行。
Sub RemoveRowsAndFormat()
Dim WS As Worksheet
Dim n As Long
Dim nlast As Long
Dim rw As Range
Dim c As Long
Dim allEmpty As Boolean
For Each WS In Worksheets
With WS
nlast = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For n = nlast To 9 Step -1
Select Case .Cells(n, 3).Value
Case "Contributions-All Other", _
"Program Fees - Youth", _
"Financial Assitance", _
"Salaries & Wages", _
"Payroll Taxes", _
"Employee Benefits", _
"Staff Training and Confer.", _
"Occupancy", _
"Supplies", _
"Telephone", _
"Postage & Shipping", _
"Promotion and Advertising", _
"Bad Debt", _
"Program Operating Expense", _
"Program Operating Net"
.Rows(n + 1).EntireRow.Insert
Case Else
allEmpty = True
For c = 4 To 11
If .Cells(n, c).Value <> "" Then
allEmpty = False
Exit For
End If
Next
'The above could be replaced by a "COUNTA", but I like this way
If allEmpty Then
.Rows(n).Delete
End If
End Select
Next n
End With
Next WS
End Sub
您在最近的评论说,一个新的问题“是不是所有的冠军都需要间隔”。如果是这样,Select Case
声明可以很容易地包括如下功能:
Select Case .Cells(n, 3).Value
'Do nothing for headings which we just want to leave alone
Case "Contributions-All Other", _
"Program Fees - Youth", _
"Financial Assitance", _
"Salaries & Wages", _
"Payroll Taxes", _
"Employee Benefits", _
"Staff Training and Confer.", _
"Occupancy", _
"Supplies"
'Process cases where an additional row needs to be inserted
Case "Telephone", _
"Postage & Shipping", _
"Promotion and Advertising", _
"Bad Debt", _
"Program Operating Expense", _
"Program Operating Net"
.Rows(n + 1).EntireRow.Insert
'For all the other rows, check whether it needs to be deleted
Case Else
allEmpty = True
'...
(显然,我刚才做了哪些标题应该有他们之后插入行,这不应该。)
这Select Case
声明只是写了以下If
声明的简化方式(?):
If .Cells(n, 3).Value = "Contributions-All Other" Or _
.Cells(n, 3).Value = "Program Fees - Youth" Or _
.Cells(n, 3).Value = "Financial Assitance" Or _
.Cells(n, 3).Value = "Salaries & Wages" Or _
.Cells(n, 3).Value = "Payroll Taxes" Or _
.Cells(n, 3).Value = "Employee Benefits" Or _
.Cells(n, 3).Value = "Staff Training and Confer." Or _
.Cells(n, 3).Value = "Occupancy" Or _
.Cells(n, 3).Value = "Supplies" Then
ElseIf .Cells(n, 3).Value = "Telephone" Or _
.Cells(n, 3).Value = "Postage & Shipping" Or _
.Cells(n, 3).Value = "Promotion and Advertising" Or _
.Cells(n, 3).Value = "Bad Debt" Or _
.Cells(n, 3).Value = "Program Operating Expense" Or _
.Cells(n, 3).Value = "Program Operating Net" Then
.Rows(n + 1).EntireRow.Insert
Else
allEmpty = True
'...
End If
PS “财务援助”应该是“财务援助”吗?
首先,'rw.Cells(n,3).Value =“贡献 - 所有其他”和rw.Cells(n,3).Value =“节目费用 - 青春”将评估为“False ' - 'rw.Cells(n,3).Value'不能等于'“贡献 - 所有其他”** **和**等于'“节目费 - 青年”'。你需要使用'Or'而不是'And'。 – YowE3K
而且我建议'Set rw = ActiveWorkbook.ActiveSheet.UsedRange.Rows'应该是'Set rw = ActiveWorkbook.ActiveSheet.UsedRange'。 – YowE3K
除了这些评论,你能告诉我们你收到了什么错误信息 - 这将使我们能够找到编译错误的原因。 – YowE3K