如果某個范圍內的單元格（在本例中為第一行）包含特定值，我想洗掉一列。我以為我可以這樣做：
Public Sub Delete_Column()
Select Case Range(A1:A10)
Case "Birthday", "Gender"
cell.EntireColumn.Delete
End Select
End Sub
但它不起作用。我確定這是Select Case Range(A1:A10)錯誤的線路，但我不知道如何修復它。
uj5u.com熱心網友回復：
洗掉列（使用Union）
Sub DeleteColumns()
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim drg As Range
Dim cell As Range
' First 10 cells (columns) in the first worksheet row.
For Each cell In ws.Range("A1").Resize(, 10).Cells
' Specified single-row range in the first worksheet row.
'For Each cell In ws.Range("A1:J1").Cells
Select Case CStr(cell.Value)
Case "Birthday", "Gender"
If drg Is Nothing Then
Set drg = cell
Else
Set drg = Union(drg, cell)
End If
'Case Else ' do nothing
End Select
Next cell
If drg Is Nothing Then Exit Sub ' no cell found
drg.EntireColumn.Delete
End Sub
uj5u.com熱心網友回復：
Range("A1:A10")不是第一行，而是第一行的 A 列。也許你的意思是Range("A1:J1")？
試試下面的修改代碼：
Public Sub Delete_Column()
Dim Rng As Range, C As Range
' set the Range object
Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10") ' <-- replace "Sheet1" with your sheet's name
' first row should be:
Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:J1") ' <-- replace "Sheet1" with your sheet's name
' loop over each cell in your Range
For Each C In Rng.Cells
Select Case C.Value
Case "Birthday", "Gender"
C.EntireColumn.Delete
End Select
Next C
End Sub
uj5u.com熱心網友回復：
這應該可以滿足您的需求。注意 Range.Find 方法的使用：
Public Sub Delete_Column()
Dim rng As Excel.Range, searchValue As Variant
Set rng = Range("A1:A10")
searchValue = "q"
If Not rng.Find(searchValue, LookIn:=xlValues) Is Nothing Then
Debug.Print "found"
Else
Debug.Print "not found"
End If
End Sub
uj5u.com熱心網友回復：
你可以：
隱藏“有效”列
洗掉可見（->“無效”）的
Sub DeleteColumns() With ThisWorkbook.Worksheets("Sheet1").Range("A1:J1") Dim cel As Range For Each cel In .Cells Select Case cel.Value Case "Birthday", "Gender" Case Else cel.EntireColumn.Hidden = True ' hide "valid" columns End Select Next On Error Resume Next ' ignore any error should no columns have been hidden .SpecialCells(xlCellTypeVisible).EntireColumn.Delete ' delete hidden columns .EntireColumn.Hidden = False ' make all "valid" columns back visible End With End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/532746.html
標籤：擅长vba句法
下一篇：重新格式化Excel資料庫