如何使用 VBA 删除除特定标题之外的所有空列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10705724/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use VBA to delete all columns which are empty apart from a specific header?
提问by seegoon
I'd like to delete all columns in a worksheet which meet the following criteria:
我想删除工作表中满足以下条件的所有列：
- row 1 = "foobar"
- rows 2-1000 are empty
- 第 1 行 = "foobar"
- 第 2-1000 行为空
It sounds simple enough but I haven't managed to get it working fully. Any help would be massively appreciated.
这听起来很简单，但我还没有设法让它完全工作。任何帮助将不胜感激。
Thanks!
谢谢！
回答by Siddharth Rout
Fastest way to delete rows as per your requirement (TRIED AND TESTED).
根据您的要求删除行的最快方法（TRIED AND TESTED）。
I am assuming that Row1 Has Column Headers
我假设 Row1 有列标题
Option Explicit
Sub Sample()
Dim aCell As Range, rng As Range
Dim LastCol As Long, LastRow As Long, i As Long
With Sheets("Sheet1")
Set aCell = .Rows(2).Find(What:="foobar", LookIn:=xlValues, _
Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then .Rows(2).Delete
LastRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
LastCol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Set rng = Range("A1:" & Split(Cells(, LastCol).Address, "$")(1) _
& LastRow)
ActiveSheet.AutoFilterMode = False
For i = 1 To LastCol
rng.AutoFilter Field:=i, Criteria1:=""
Next i
rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End With
End Sub
回答by Alex K.
How about
怎么样
dim col as Long, lastCol as Long, r as range
lastCol = ActiveSheet.Usedrange.columns(Activesheet.Usedrange.columns.count).column
for c=lastCol to 1 Step -1
set r = Range(Cells(1, c), Cells(1000, c))
if r.Rows(1) = "foobar" Then
if WorksheetFunction.CountA(Range(r.Rows(2), r.Rows(r.Rows.Count))) = 0 then
Columns(c).delete
end if
end If
next
[edit by OP: added a missing space]
[由 OP 编辑：添加了一个缺失的空间]
