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

¦X¨Ö¦h­Ó¤u§@ªí¤º¸ê®Æ©ó·s¼W1¤u§@ªí

¦X¨Ö¦h­Ó¤u§@ªí¤º¸ê®Æ©ó·s¼W1¤u§@ªí

»Ý­n¦X¨Ö¦h­Ó¤u§@ªí¤º¸ê®Æ©ó·s¼W1¤u§@ªí
¥B¨Ì¤u§@ªí¼ÐÅҲΤ@§e²{©ó·s¼Wªº¤u§@ªí¤¤ªº¬Y¤@Äæ
¥Ø«e¥HVBA¶×¤J¦h¤å¦r¸ê®Æ¦ý¤ÀÄݤ£¦PÀÉ®×
¦X¨Ö¼Æ¾Ú-1¡B¦X¨Ö¼Æ¾Ú-2¡B¦X¨Ö¼Æ¾Ú-3
¦ý§Æ±æ¶×¤J¸ê®Æ§e²{¦p ªþ¥ó¤¤ªº ¦X¨Ö¼Æ¾Ú .xls
¶×¤J¸ê®Æ³B²z¤è­±ÁÙ OK¥B¸ê®Æ¦h¡A´N¨Ì»Ý¨D²¤Æ¦¨ªþ¥ó¤¤ªº¸ê®Æ
¤£ª¾¬O§_¯à¥HVBA ¤è¦¡´î¤Ö¥Ø«e¤â°Ê¤è¦¡³B²z§@·~¡A
·Ð½Ð¬O§_°ª¤â¥i¨ó§U³B²z¸Ñ¨M¡A·P®¦¡I¡I

¦X¨Ö¼Æ¾Ú.rar (9.58 KB)

  1. Sub nn()
  2.   Dim iI%, iRowEnd%, iTarRow%
  3.   Dim sGroup$
  4.   Dim vMer
  5.   
  6.   Set vMer = Sheets("¦X¨Ö")
  7.   iTarRow = 2
  8.   
  9.   For iI = 1 To Sheets.Count
  10.     With Sheets(iI)
  11.       If .Name <> vMer.Name Then
  12.         sGroup = .Name
  13.         iRowEnd = .Cells(Rows.Count, 1).End(xlUp).Row
  14.         .Range(.Cells(2, 1), .Cells(iRowEnd, 2)).Copy
  15.         With vMer
  16.           .Paste Destination:=.Cells(iTarRow, 1)
  17.           .Cells(iRowEnd + iTarRow - 2, 3) = sGroup
  18.           .Range(.Cells(iTarRow, 3), .Cells(iRowEnd + iTarRow - 2, 3)).FillUp
  19.         End With
  20.         iTarRow = iRowEnd + iTarRow - 1
  21.       End If
  22.     End With
  23.   Next iI
  24. End Sub
½Æ»s¥N½X

TOP

·PÁÂluhproªº¨ó§U¡A´ú¸Õ²Å¦X»Ý¨D
¥¿¦b¬ã¨s±zªº»yªkÀ³¥Î©ó»Ý¨D¤¤¡A
¦ý¥H§Úªº¥\¤O»P®É¶¡¡AÁٻݭn¤@¨Ç®É¶¡¡A­Y¦³°ÝÃD¦A¦V±z°Q±Ð
¬Û«H¤@©w¯à¥\¯à¤j¼W¡A·P®¦

TOP

²¤Æ¤@¤U
  1. Sub Ex()
  2.     Dim Sh As Worksheet, i%, iRowEnd%, Ar()
  3.     With Sheets("¦X¨Ö")
  4.         .Move Sheets(1)                      '¤u§@ªí²¾¨ì³Ì«e­±
  5.         .UsedRange.Offset(1).ClearContents   '¨Ï¥Î½d³ò²Ä2¦C¶}©l²M°£
  6.         For i = 2 To Sheets.Count            '¤u§@ªí¯Á¤Þ2¶}©lªº°j°é
  7.             iRowEnd = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
  8.             Ar = Sheets(i).Range("A2:B" & iRowEnd).Value                                            '¨ú±o¸ê®Æ
  9.             .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(Ar), 2) = Ar                 '¸m¤J¸ê®Æ
  10.             .Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(Ar), 1) = Sheets(i).Name     '¸m¤J¤u§@ªí¦WºÙ
  11.         Next
  12.     End With
  13. End Sub
½Æ»s¥N½X

TOP

®tÂI³£§Ñ°O¦^¨Ó¬Ý¬Ý¦Û¤vªº°ÝÃD¡A·Q¤£¨ìÁÙ¦³¨ä¥L¤è¦¡
¬ã¨s¤¤¡AGBKEEª©¤j«á­±ÁÙ¤S¥[µù¸Ñ¡A¹ï©ó¾Ç²ß¤¤ªº§Ú¤F¸ÑÀ³¸Ó·|§Ö¨Ç
©ñ°²®É¬ã¨s¡A¦³°ÝÃD¦A°Q±Ð¡A·P®¦

TOP

¾Ç²ß¤¤¡A¹ï©ó»yªkÀ´±o¤£¦h¡A·Ð½Ð  GBKEEª©¤j«ü¾É¤@¤U
iRowEnd = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row
ªº·N¸q¬°¦ó?
Ãö©ó  Ar = Sheets(i).Range("A2:B" & iRowEnd).Value ¤¤ªº Range ("A2:B" & iRowEnd)ªº·N«ä¬O?
¤@¤U¤l­n±q°ò¥»VBA»{ª¾¸õ¨ì¬Ý±oÀ´ª©¤jªº»yªk¡A«ç»ò"»¡©ú"»P¤u¨ã®Ñ³£¥¢®ÄÅo¡I¡I

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-13 09:28 ½s¿è

¦^´_ 6# Happkkevin
iRowEnd = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Rowªº·N¸q¬°¦ó?
A: iRowEnd =Sheets(i).A65536©¹¤W¨ì¦³¸ê®ÆÀx¦s®æªº¦C¸¹
i  ->¤u§@ªíªº¯Á¤Þ­È
Rows.Count  ¦Cªº­p¼Æ(Á`¼Æ)    2003¬° 65536   
End ÄÝ©Ê      ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥]§t¨Ó·½½d³ò¤§°Ï°ìµ²§À³BªºÀx¦s®æ¡Cµ¥©ó«ö END+¦V¤WÁä(XlUp)¡BEND+¦V¤UÁä(XlDown)¡BEND+¦V¥ªÁä    (XlToLeft)©Î END+¦V¥kÁä(XlToRight)¡C°ßŪ Range ª«¥ó¡C
Row
->¦C¸¹

Ãö©ó  Ar = Sheets(i).Range("A2:B" & iRowEnd).Value ¤¤ªº Range ("A2:B" & iRowEnd)ªº·N«ä¬O?
A: ¦p  iRowEnd=100     Range ("A2:B" & iRowEnd) -> Range ("A2:B100")

TOP

Dim Ar()
GBKEE µoªí©ó 2011-7-6 08:52

Ar() ¬°«Å§i¬°°}¦CªºÁY¼g¶Ü¡H

Resize(UBound(Ar), 2)
GBKEE µoªí©ó 2011-7-6 08:52

¾Ç²ß ResizeÀ³¥Î¡A¸`¿ý »¡©úÀɶ¶¹D¶K¤W¨Ñ°Ñ¦Ò
¦ý¨ä¤¤ UBound(Ar)ªº§t¸q»P¥Îªk¬°¦ó?
  1. Resize ÄÝ©Ê
  2. ½Ð°Ñ¾\½Ð°Ñ¾\½Ð°Ñ¾\½Ð°Ñ¾\½Õ¾ã«ü©wªº½d³ò¡C¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí½Õ¾ã«áªº½d³ò¡C

  3. expression.Resize(RowSize, ColumnSize)
  4. expression     ¥²¿ï¡C¸Ó¹Bºâ¦¡¶Ç¦^­n½Õ¾ã¤j¤pªº Range ª«¥ó¡C

  5. RowSize     ¿ï¾Ü©Êªº Variant¡C·s½d³ò¤¤©Ò¥]§tªº¦C¼Æ¡C¦pªG¬Ù²¤¦¹¤Þ¼Æ¡A½d³ò¤¤ªº¦C¼Æ«O«ù¤£ÅÜ¡C

  6. ColumnSize     ¿ï¾Ü©Êªº Variant¡C·s½d³ò¤¤©Ò¥]§tªºÄæ¼Æ¡C¦pªG¬Ù²¤¦¹¤Þ¼Æ¡A½d³ò¤¤ªºÄæ¼Æ«O«ù¤£ÅÜ¡C

  7. ½d¨Ò
  8. ¦¹½d¨Ò½Õ¾ã Sheet1 ¤¤¿ï¾Ü½d³òªº¤j¤p¡A¨Ï¤§¼W¥[¤@¦C©M¤@Äæ¡C

  9. Worksheets("Sheet1").Activate
  10. numRows = Selection.Rows.Count
  11. numColumns = Selection.Columns.Count
  12. Selection.Resize(numRows + 1, numColumns + 1).Select
  13.                
  14. ¦¹½d¨Ò°²³]¦b Sheet1 ¤¤¦³¤@­Ó¥]§t¼ÐÃD¦Cªºªí®æ¡C¦¹½d¨Ò¿ï©w¸Óªí®æ¡A¦ý¤£¿ï©w¼ÐÃD¦C¡C°õ¦æ¦¹½d¨Ò¤§«e¡A²{¥ÎÀx¦s®æ¥²¶·³B©ó¸Óªí®æ¤¤¡C

  15. Set tbl = ActiveCell.CurrentRegion
  16. tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
  17.     tbl.Columns.Count).Select
½Æ»s¥N½X

TOP

¦^´_ 8# Happkkevin
UBound ¨ç¼Æ   ¶Ç¦^ Long­È¡Aªí¥Ü«ü©w°}¦C¬Yºû³Ì¤j¥i¨Ï¥Îªº°}¦C¯Á¤Þ¡C
Dim A(1 To 100, 0 To 3, -3 To 4)   
³¯­z¦¡ ¶Ç¦^­È
UBound(A, 1) -> UBound(A) -> 100
UBound(A, 2)  ->3
UBound(A, 3)  ->4

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦¨´N¤@¤Á¡j®É¶¡¥i¥H³y´N¤H®æ¡A¥i¥H¦¨´N¨Æ·~¡A¤]¥i¥HÀx¿n¥\¼w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD