[µo°Ý] ¨Ì·Ó¼ÐÃDÃöÁä¦r«ü©wÄæ¦ì±ø¥ó¦Ó§R°£¸ÓÄd

billchenfantasy

½Ð°Ý
Sub «ö¶s1_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
¥H¤U¬O±N§¹¥þ²Å¦X"PLAN_NO", "PlAN_DAT"ªº¼ÐÃD¨äÄæ¦ì§R¥h¡A¦ý¦]¬°¦³¨ÇÄæ¦ì¨ã¦³PLAN_NO_1,PLAN_NO_2......½Ð°Ý­n¦p¦ó­×§ï¦¨²Å¦XÃöÁä¦r"PLAN_NO", "PlAN_DAT"ªºÄæ¦ì§R¥hªº¼gªk
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

End Sub

billchenfantasy

¹ï¤£°_§Ú¥´¿ù¤F§ï¦¨"¥H¤U¬O±N§¹¥þ²Å¦X"PLAN_NO", "PlAN_DAT"ªº¼ÐÃD¥H¥~ªº¨ä¥LÄæ¦ì§R¥h¡A¦ý¦]¬°¦³¨ÇÄæ¦ì¨ã¦³PLAN_NO_1,PLAN_NO_2......½Ð°Ý­n¦p¦ó­×§ï¦¨²Å¦XÃöÁä¦r"PLAN_NO", "PlAN_DAT"ªºÄæ¦ì¯d¤U¨ä¥L§R¥hªº¼gªk

oobird

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

­º¥ýÁÂÁ±zªº¦^µª~§ï§¹µ{¦¡¥i¥H¶]~¦ý¬O¨S¦³¦¨¥\§R°£@@~¥H¤U¬O§ÚCOªº«e­±´X­ÓÄæ¦ì¼ÐÃD
(FID_plan0) (PLAN_AREA)  (PLAN_NO) (PLAN_NAME) (PLAN_DATE) (FID_plan01) (Plan_Are_1) (Plan_No_1) (Plan_Nam_1) (Plan_Dat_1) (FID_plan2) (PLAN_ARE_2) (PLAN_NO_2)  (Plan_Nam_2)( (PLAN_DAT_2)
­n«ç»ò§ï¼g¤~¯à§â
(PLAN_NO) (PLAN_DATE) (Plan_No_1) (Plan_Dat_1) (PLAN_NO_2) ) (PLAN_DAT_2)
Äæ¦ì¯d¤U¡A¨ä¥LªºÄæ¦ì§R°£©O?

billchenfantasy

¦^´_ 3# oobird

­º¥ýÁÂÁ±zªº¦^µª~§ï§¹µ{¦¡¥i¥H¶]~¦ý¬O¨S¦³¦¨¥\§R°£@@~¥H¤U¬O§ÚCOªº«e­±´X­ÓÄæ¦ì¼ÐÃD
(FID_plan0) (PLAN_AREA)  (PLAN_NO) (PLAN_NAME) (PLAN_DATE) (FID_plan01) (Plan_Are_1) (Plan_No_1) (Plan_Nam_1) (Plan_Dat_1) (FID_plan2) (PLAN_ARE_2) (PLAN_NO_2)  (Plan_Nam_2)( (PLAN_DAT_2)
­n«ç»ò§ï¼g¤~¯à§â
(PLAN_NO) (PLAN_DATE) (Plan_No_1) (Plan_Dat_1) (PLAN_NO_2) ) (PLAN_DAT_2)
Äæ¦ì¯d¤U¡A¨ä¥LªºÄæ¦ì§R°£©O?

Hsieh

¦^´_ 5# billchenfantasy

1. Sub ex()
   
2. Dim Rng As Range, C As Range
   
3. ar = Array("PLAN_NO", "PlAN_DAT")
   
4. For Each C In Rows(1).SpecialCells(xlCellTypeConstants)
   
5. n = 0
   
6.    For Each a In ar
   
7.      If InStr(UCase(C), UCase(a)) > 0 Then
   
8.      n = n + 1
   
9.      End If
   
10.      If n = 0 Then
    
11.         If Rng Is Nothing Then
    
12.            Set Rng = C
    
13.            Else
    
14.            Set Rng = Union(Rng, C)
    
15.         End If
    
16.      End If
    
17.   Next
    
18. Next
    
19. Rng.EntireColumn.Delete
    
20. End Sub

½Æ»s¥N½X

billchenfantasy

¦^´_ 6# Hsieh
@@·PÁ±zªºµªÂСA¥H¤U¬O§Ú¨Ì¾Ú±zªº¼¶¼g²Õ¦X¦Ó¦¨ªº»Ý¨D¡A¹B¦æ¥¿±`¡A"PLAN_NO"¥i³Q¿z¥X¦ýºû"PLAN_DAT"µLªk§P§O¥X¨Ó
Sub «ö¶s1_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
¥H¤Wµ²ªG¬°(PLAN_NO) (Plan_No_1) (PLAN_NO_2) ¦Ó«D
(PLAN_NO) (PLAN_DATE) (Plan_No_1) (Plan_Dat_1) (PLAN_NO_2) (PLAN_DAT_2)Áٽаݦp¦ó­×§ï·PÁ±z

billchenfantasy

¦^´_ 7# billchenfantasy
­×§ï«e­ì©l¸ê®Æ.rar (10.58 KB)

¦¹¬°­ì©l®æ¦¡
¤U¸ü¦¸¼Æ: 4

2013-2-18 11:03

¥H¤W¬°­ì©l®æ¦¡¸ê®Æ·P®¦~

Hsieh

¦^´_ 8# billchenfantasy
²Ä17¦æªºNext­n©¹«e©ñ

1. Sub ex()
   
2. 
   
3. Dim Rng As Range, C As Range
   
4. ar = Array("PLAN_NO", "PLAN_DAT")
   
5. For Each C In Rows(1).SpecialCells(xlCellTypeConstants)
   
6. n = 0
   
7.    For Each a In ar
   
8.      If InStr(UCase(C), UCase(a)) > 0 Then
   
9.      n = n + 1
   
10.      End If
    
11.    Next
    
12.      If n = 0 Then
    
13.         If Rng Is Nothing Then
    
14.            Set Rng = C
    
15.            Else
    
16.            Set Rng = Union(Rng, C)
    
17.         End If
    
18.      End If
    
19. Next
    
20. Rng.EntireColumn.Delete
    
21. End Sub

½Æ»s¥N½X

billchenfantasy

¦^´_ 9# Hsieh
= =­ì¨Ó¦p¦¹¡A­è­è¨Ï¥Î«áµ{¦¡¦¨¥\¹B§@~·PÁ±z~