請問
Sub 按鈕1_Click()
Dim uFile$
Dim rng As Range
ChDrive "C:\"
ChDir "C:\Users\us\Desktop\"
Source = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx),*.xlsx")
With Workbooks.Open(Source)
For i = 1 To ActiveWorkbook.Sheets.Count
.Sheets(i).Copy after:=ThisWorkbook.Worksheets(Sheets.Count)
Next i
.Close
End With
以下是將完全符合"PLAN_NO", "PlAN_DAT"的標題其欄位刪去，但因為有些欄位具有PLAN_NO_1,PLAN_NO_2......請問要如何修改成符合關鍵字"PLAN_NO", "PlAN_DAT"的欄位刪去的寫法 With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
For j = 1 To .[A1].CurrentRegion.Columns.Count
If IsError(Application.Match(.Cells(1, j).Value, Array("section", "PLAN_NO", "PlAN_DAT"), 0)) Then
If rng Is Nothing Then Set rng = .Columns(j) Else Set rng = Union(rng, .Columns(j))
End If
Next j
.Range(rng.Address).Delete Shift:=xlToLeft
Set rng = Nothing
End With
If IsError(Application.Match(.Cells(1, j).Value, Array("section", "PLAN_NO", "PlAN_DAT"), 0)) Then
改成
if .cells(1,j) like "PLAN_NO*" or .cells(1,j) like "PlAN_DAT*" then作者: billchenfantasy 時間: 2013-2-8 09:48
For Each C In Rows(1).SpecialCells(xlCellTypeConstants)
n = 0
For Each a In ar
If InStr(UCase(C), UCase(a)) > 0 Then
n = n + 1
End If
If n = 0 Then
If Rng Is Nothing Then
Set Rng = C
Else
Set Rng = Union(Rng, C)
End If
End If
Next
Next
Rng.EntireColumn.Delete
End Sub
複製代碼
作者: billchenfantasy 時間: 2013-2-18 10:27
本帖最後由 billchenfantasy 於 2013-2-18 10:28 編輯
回復 6#Hsieh
@@感謝您的答覆，以下是我依據您的撰寫組合而成的需求，運行正常，"PLAN_NO"可被篩出但維"PLAN_DAT"無法判別出來
Sub 按鈕1_Click()
Dim uFile$
Dim Rng As Range, C As Range
ChDrive "C:\"
ChDir "C:\Users\us\Desktop\"
Source = Application.GetOpenFilename(FileFilter:="Excel Files (*xlsm.),*.xlsm")
With Workbooks.Open(Source)
For i = 1 To ActiveWorkbook.Sheets.Count
.Sheets(i).Copy after:=ThisWorkbook.Worksheets(Sheets.Count)
Next i
.Close
End With
ar = Array("PLAN_NO", "PLAN_DAT")
For Each C In Rows(1).SpecialCells(xlCellTypeConstants)
n = 0
For Each a In ar
If InStr(UCase(C), UCase(a)) > 0 Then
n = n + 1
End If
If n = 0 Then
If Rng Is Nothing Then
Set Rng = C
Else
Set Rng = Union(Rng, C)
End If
End If
Next
Next
Rng.EntireColumn.Delete
End Sub
以上結果為(PLAN_NO) (Plan_No_1) (PLAN_NO_2) 而非
(PLAN_NO) (PLAN_DATE) (Plan_No_1) (Plan_Dat_1) (PLAN_NO_2) (PLAN_DAT_2)還請問如何修改感謝您作者: billchenfantasy 時間: 2013-2-18 11:04
