ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð±Ð§ï¨}³t«×¤èªk

¦^´_ 7# 198188
§AÀ³¸Óª`·N¤@¤UGª©ªºÁY¹Ï ,
¥L³o¼Ë°µ¤w¸g±N±ø¥ó°Ï©M¿z¿ïµ²ªG¤À¶}¤F,
§Aªºsheet2¤@¶}©l¬O§¹¥þªÅ¥Õ

TOP

¦^´_ 10# 198188
°Ñ¦Ò¬Ý¬Ý , §Q¥Î¶i¶¥¿z¿ï°µªº
Â^¨ú.PNG
2012-12-2 14:47

test.zip (19.54 KB)
  1. Sub myFilter()
  2.     Dim rngSrc As Range, rngCopyField As Range, rngFilter As Range
  3.     Dim nextRow As Long, endRow As Long
  4.    
  5.     Set rngSrc = Sheets("¸ê®Æ®w").[A1:G7]
  6.     Set rngCopyField = Sheets("±ø¥ó°Ï").[B8:H8]
  7.     Set rngFilter = Sheets("±ø¥ó°Ï").[B1].Resize(Sheets("±ø¥ó°Ï").[B1].CurrentRegion.Rows.Count, 8)
  8.    
  9.     nextRow = Sheets("¾ã²z°Ï").UsedRange.Rows.Count + 1
  10.                
  11.     rngSrc.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
  12.     rngFilter, CopyToRange:=Sheets("¾ã²z°Ï").Range("A" & nextRow)
  13.         
  14.     endRow = Sheets("¾ã²z°Ï").UsedRange.Rows.Count
  15.    
  16.     For i = 1 To rngCopyField.Count
  17.         If rngCopyField(i) = "N" Then
  18.             Sheets("¾ã²z°Ï").Range(nextRow & ":" & endRow).Columns(i).Clear
  19.         End If
  20.     Next
  21.    
  22.     Sheets("¾ã²z°Ï").Range("A" & nextRow).Resize(1, 7).Delete Shift:=xlUp   'delete header
  23.    
  24.     Set rngSrc = Nothing
  25.     Set rngCopyField = Nothing
  26.     Set rngFilter = Nothing
  27. End Sub
½Æ»s¥N½X

TOP

¦^´_ 12# 198188
±ø¥ó³]©w³£¸ò¶i¶¥¿z¿ï·Ç«h¤@¼Ë
§A¥i¥H°Ñ¦Ò  http://office.microsoft.com/zh-tw/excel-help/HP010073942.aspx
¦p¿é¤J
=" "        ¿z¿ï¸ÓÄæ¦ì¬°³æ­ÓªÅ¥Õ¦r¤¸
="* *"    ¿z¿ï¸ÓÄæ¦ì¥]§tªÅ¥Õ¦r¤¸
=" *"      ¿z¿ï¸ÓÄæ¦ì¥HªÅ¥Õ¦r¤¸¶}ÀY

TOP

¦^´_ 14# 198188
§äªÅ¥ÕÀx¦s®æ , ¿é¤J
=   ©Î   ="="

TOP

¦^´_ 17# 198188

="=KEN"

test1.zip (723.23 KB)
1.  §AÄæ¼Æ¦@¦³38Äæ resize¤]­n¸òµÛ§ï
2.  Result¤u§@ªí­n²MªÅ­Èªº®É­Ô , ­n¥Î¾ã¦C§R°£ ,
     §_«h§PÂ_Sheets("Result").UsedRange.Rows.Count¦C¼Æ®É
     ÁÙ·|¬O²MªÅ«eªº¦C¼Æ , ³oÂI§Ú¤]¤£ª¾¹D¦ó¸Ñ...

TOP

¦^´_ 19# 198188
¥i¥H¦Û¤v§ïªü , §Ú¬O¥H¬°§A­n¤@ª½·s¼W¤~³o¼Ë¼gªº..
    'nextRow = Sheets("Result").UsedRange.Rows.Count + 1
    nextRow = 2
    Sheets("Result").UsedRange.Offset(1).Clear
    ---²¤---
    'endRow = Sheets("Result").UsedRange.Rows.Count
    endRow = Sheets("Result").Range("A" & Sheets("Result").Rows.Count).End(xlUp)

TOP

        ÀR«ä¦Û¦b : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD