如果单元格值为空,我希望删除多个工作表中的行(仅删除工作簿中的特定行)。请注意,行中的其余字段确实包含数据。到目前为止,我有以下内容,但是不确定如何指定工作表。有人能帮上忙吗?
Sub sbDelete_rows_if_cell_blank()
Dim lRow As Long
Dim iCntr As Long
lRow = 2000
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 1).Value = "" Then
Rows(iCntr).Delete
End If
Next
End Sub 发布于 2015-06-20 20:02:40
将您的代码放入此循环中将遍历此代码所在的工作簿中的所有工作表,并在每个工作表中运行您的代码。
Sub sbDelete_rows_if_cell_blank()
Dim lRow As Long
Dim iCntr As Long
Dim ws as Worksheet
For each ws in ThisWorkbook.Worksheets
' Find last row in column A
lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
For iCntr = lRow To 1 Step -1
If ws.name<>"Sheet1" and ws.name <> "Sheet2" then ' change this line to the sheet names you want to leave out.
If IsEmpty(ws.Cells(iCntr, 1)) Or Trim(ws.Cells(iCntr, 1).Value) = "" Then
ws.Rows(iCntr).Delete
End If
end if
Next iCntr
Next ws
End Sub使用D_Bester对 if condition的建议更新了
更新2:查看评论
这将实现我认为您想要实现的目标
Sub Combine()
Dim nws, ws As Worksheet
Dim rng As Range
' Add New Sheet
On Error Resume Next
Set nws = ThisWorkbook.Sheets("Combined")
If nws Is Nothing Then
With ThisWorkbook.Sheets
Set nws = .Add(After:=Sheets(.Count))
nws.Name = "Combined"
End With
End If
On Error GoTo 0
For Each ws In ThisWorkbook.Sheets
If Not ws.Name = nws.Name Then
With ws
Set rng = Range(.Cells(1, 1), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count))
rng.Copy Destination:=nws.Cells(nws.Cells(nws.Rows.Count, "A").End(xlUp).Row + 1, 1)
End With
End If
Next ws
End Sub发布于 2015-06-20 21:31:20
您可以遍历工作表,然后使用特殊单元格删除空白。Yoi还可以设置循环,这样它就不会删除“Sheet1”中的空格(在本例中)
Sub DeleteBlnkRows()
Dim sh As Worksheet
For Each sh In Sheets
If sh.Name <> "Sheet1" Then
sh.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
Next sh
End Subhttps://stackoverflow.com/questions/30953715
复制相似问题