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

[µo°Ý] ¤£¦Pªºsheet¬Û¦Pªº°Ê§@,¦p¦óÅý¥¦¤@¦¸©Ê§¹¦¨?

¦^´_ 30# oobird
¤j®v,
±Ï©R,§Ú­ì¥ýªº»Ý¨D¬O±N°Ê§@1: AA:AL      ªº½d³òÅܦ¨­È(´N¬O±N¤½¦¡¥h°£)
¦ý§Úµo²{³o­Ó¼g±N¾ã­Ó¤u§@ªí©Ò¦³¤º®e³£Åܦ¨­È,¦Ó¥BÅܦ¨­È¤§«á,­ì¨Ó®æ¦¡³£Åܦ¨©Ç©Çªº¤é´Á®æ¦¡.(¦pªþ¥ó,çP¦â©³ªº¼Ðµù),
EX.zip (20.96 KB)
¥H¤U¥¨¶°¸Ó¦p¦ó­×§ï¦¨¥u±NAA:AL¦³¸ê®Æªº½d³òÅܦ¨­È?
  1.    Workbooks("2012 BCMart Chart.xlsx").Activate
  2.    For s = 5 To 10 '±q²Ä¬Y­Ó¤u§@ªí°õ¦æ¦Ü²Ä¬Y­Ó
  3.    Sheets(s).Activate
  4.    Set Sh = ActiveSheet
  5.             Sh.UsedRange = Sh.UsedRange.Value    '§â¤½¦¡µ²ªGÅܦ¨­È
  6.             Sh.AutoFilterMode = False     '¨ú®ø¦Û°Ê¿z¿ï
  7.             Sh.[a4:ad4].AutoFilter    '«Ø¥ß¦Û°Ê¿z
  8.     Next    '¤U¤@­Ó¤u§@ªí
½Æ»s¥N½X

TOP

Sh.UsedRange = Sh.UsedRange.Value
§ï¬°
Sh.[aa:al] = Sh.[aa:al].Value

TOP

¦^´_ 33# oobird
¤j®v,
µ{¦¡§ï¦¨³o¼Ë¬O¤£·|±N¨ä¥¦ªºÀx¦s®æ¦³¤½¦¡ªº¦a¤èÅܦ¨­È,¦ý¤]¥X²{¤F¥i©Èªº"Àt³t",¥H«e¥þ³¡Àx¦s®æ¤@°_§@·~«D±`§Ö³tªº,²{¦b¬O¤@­Ó­Ósheet.."Àt³t"§@·~,³oÂI¯à°÷§ïµ½¶Ü?  

TOP

  1. Workbooks("2012 samples Chart.xlsx").Activate
  2.    For s = 2 To 7 '±q2ndªº¤u§@ªí°õ¦æ¦Ü7st
  3.    Sheets(s).Activate
  4.    Set sh = ActiveSheet
  5.    n = sh.UsedRange.Rows.Count
  6.             sh.[aa5].Resize(n, 12) = sh.[aa5].Resize(n, 12).Value    '§â¤½¦¡µ²ªGÅܦ¨­È
  7.             sh.AutoFilterMode = False     '¨ú®ø¦Û°Ê¿z¿ï
  8.             sh.[a4:ad4].AutoFilter    '«Ø¥ß¦Û°Ê¿z
½Æ»s¥N½X
¨º§ï³o¼Ë§a¡A§Ú¨S¥Î2010«ÜÃø·Q¹³¤@¦Ê¦h¸U¦C¶]°_¨Ó¬O¤°»ò³t«×¡C

TOP

¦^´_ 35# oobird
¤j®v,
§Ú¸Õ¤F¤@¤U,¸ò­ì¨Óªº¤@¼Ë,·|¥X²{"¶ñº¡Àx¦s®æ"ªº¦r¼Ë,µM«ásheet.."Àt³t"§@·~,ÁÙ¦³§Oªº»yªk¥i¥HÅý§Ú¦A¸Õ¸Õ¬Ý¶Ü?«ô°U¤F.

TOP

À³¸Ó n = sh.UsedRange.Rows.Count (¨Ï¥Î¤¤ªºÁ`¦C¼Æ)¤£·|¨ú¹L¤jªº½d³ò
©p¥i¥H¥Îf8Áä³v¥y¹B¦æ¡A¬Ý¬Ý¹B¦æ¨ì³o¤@¦æ®Énªº­È¬O§_¤u§@ªíªº³Ì«á¤@¦C¡C
­Y¤£¬O´N¶·­n§â©³¤UªºªÅ¥Õ¥þ³¡¿ï¤¤«ödelete²M°£
¤]¥i¥H§ï¤@¤U³o¥y¸Õ¸Õ¡G
n=sh.[d10000].end(3).row (DÄ榳¸ê®Æªº³Ì«á¦C)

TOP

¦^´_ 37# oobird
¤j®v:

1)   n = sh.UsedRange.Rows.Count '(¨Ï¥Î¤¤ªºÁ`¦C¼Æ)¤£·|¨ú¹L¤jªº½d³ò....sheet..ÁÙ¬O"Àt³t",¦Ó¥B,§Ú»Ý­nªº¬OAA:AL,³o­Ó»yªk¥u­n±NAA:ACÅܦ¨­È,¦]¬°AD:AE¬OªÅ¥Õ¦C
2)   n=sh.[d10000].end(3).row (DÄ榳¸ê®Æªº³Ì«á¦C)....³o­Ó»yµLªk°õ¦æ
  1.    Workbooks("2012 BCMart Chart.xlsx").Activate
  2.    For s = 5 To 10 '±q²Ä¬Y­Ó¤u§@ªí°õ¦æ¦Ü²Ä¬Y­Ó
  3.    Sheets(s).Activate
  4.    Set sh = ActiveSheet
  5.    n = sh.[d10000].End(3).Row '(DÄ榳¸ê®Æªº³Ì«á¦C)
  6.   'Sh.UsedRange = Sh.UsedRange.Value    '§â¦³¸ê®ÆªºÀx¦s®æ,¤½¦¡µ²ªGÅܦ¨­È
  7.             sh.[aa:al] = sh.[aa:al].Value  '§â¤½¦¡µ²ªGÅܦ¨­È
  8.             sh.AutoFilterMode = False     '¨ú®ø¦Û°Ê¿z¿ï
  9.             sh.[a4:ad4].AutoFilter    '«Ø¥ß¦Û°Ê¿z
  10.     Next    '¤U¤@­Ó¤u§@ªí
  11.     ActiveWorkbook.Save
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ oobird ©ó 2012-5-10 11:22 ½s¿è

©p¨S¦³§ï¨ì§r¡H§Ú¦A¾ã²z¤@¤U¡G
Workbooks("2012 BCMart Chart.xlsx").Activate
   For s = 5 To 10 '±q²Ä¬Y­Ó¤u§@ªí°õ¦æ¦Ü²Ä¬Y­Ó
   Sheets(s).Activate
   Set sh = ActiveSheet
   n = sh.[d10000].End(3).Row '(DÄ榳¸ê®Æªº³Ì«á¦C)³o¸Ì¥i¥H§ï¦¨©pªº¸ê®Æ¤¤³Ì»ôªº¤@Äæ
            sh.[aa5].Resize(n, 3) = sh.[aa5].Resize(n, 3).Value '§âaa:acÅܦ¨­È
            sh.AutoFilterMode = False     '¨ú®ø¦Û°Ê¿z¿ï
            sh.[a4:ad4].AutoFilter    '«Ø¥ß¦Û°Ê¿z
    Next    '¤U¤@­Ó¤u§@ªí

TOP

¦^´_ 39# oobird

¤j®v,
§Ú§ï¦n¨Ã­«·sRUN¤@¦¸¤F,¸ò¨ä¥Lªº»yªk¤@¼Ë,¦pªG¥u¬O±NAA:ALÅܦ¨­Èªº³£¶]±o«D±`ºC³t,½Ð°Ý³o¼Ë¬O¤£¬OµL¸Ñ¤F?

TOP

AA:AL¦b2010¤¤¬O¼Æ¤d¸U­ÓÀx¦s®æ¡A©Ò¥H·|©Ô®æ
§Ú¨S¦Ò¼{©p¬O¥Î2010¡A©Ò¥H»´²vªºµ¹©p³o­Ó»yªk¡C
©p¥Î«á¨Óªº n = sh.[d10000].End(3).Row
            sh.[aa5].Resize(n, 3) = sh.[aa5].Resize(n, 3).Value
´N¤£·|©Ô®æ°Õ¡I

TOP

        ÀR«ä¦Û¦b : ±o²z­nÄǤH¡A²zª½­n®ð©M¡C
ªð¦^¦Cªí ¤W¤@¥DÃD