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

½Ð°Ý¡GÃö©ó­×§ïVBA»yªk

¦^´_ 10# GBKEE
Dear ª©¥D¡ATEST OK
¥t¥~½Ð±Ð¤@¤U¡A­þ¤@¬q»yªk¬O±NAÄæ¦C¤é´Á¤UªÅ¥ÕÄæ®É¬°¦Û°Ê½Æ»s¤W¤@Äæ¤é´Á¡H
Just do it.

TOP

¦^´_ 11# jsc0518
¨âªÌ³£¥i¥H
  1. With ActiveSheet.UsedRange.Columns("A")
  2.         If .Cells(1).End(xlDown).End(xlDown).Row <> Rows.Count Then  '§PÂ_¦³ªÅ¥ÕªºÀx¦s®æ
  3.             .SpecialCells(xlCellTypeBlanks).Cells = "=R[-1]C"                        '¨S¦³ªÅ¥ÕªºÀx¦s®æ .SpecialCells(xlCellTypeBlanks) µ{¦¡·|¿ù»~
  4.             .Value = .Value
  5.         End If
  6.    End With
½Æ»s¥N½X
  1.     For Each E In ActiveSheet.UsedRange.Columns("A").Cells
  2.         If E(1, 3) <> "" And E = "" Then E = E.Cells(0)      
  3.       'E(1, 3) => E.Cells(1, 3) ,E.Cells(0)=> E.Cells(0, 1) ** 0 -> ¤W¤@¦C
  4.     Next
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 12# GBKEE
Dear ª©¥D,
ÁÂÁ±zªº«ü¾É
Just do it.

TOP

¦^´_ 12# GBKEE
Dear ª©¥D
©êºp¦A»P±z½Ð±Ð¤@­Ó°ÝÃD¡A§Ú·Q§â¾ã²z¹L«á¤§¸ê®Æcopy¨ì¥t¤@¤u§@ªí(³o¤u§@ªíªº¸ê®Æ¬O«ùÄò¦³¸ê®Æ²Ö­pªº¡A¸ê®Æ¥u·|Åܧó¦h)
­n«ç»ò¥ÎVBA¼g¦Ü¸Ó¤u§@ªí
¤u§@ªí"VBA"±qAÄæ¨ìHÄæ­nCOPY¨ì¤u§@ªí"¸ê®Æ®w"¤¤
*­ì¤u§@ªí"¸ê®Æ®w"¤¤ªº¸ê®Æ¤´­n«O¯d
    TT-2.rar (139.92 KB)
Just do it.

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-2-8 07:08 ½s¿è

¦^´_ 14# jsc0518
  1. Option Explicit
  2. Sub Step1()
  3.     Dim E As Range, Rng As Range
  4.     With Sheets("vba")  'With ³¯­z¦¡ ¦b¤@­Ó³æ¤@ª«¥ó©Î¤@­Ó¨Ï¥ÎªÌ¦Û­q«¬ºA¤W°õ¦æ¤@¨t¦Cªº³¯­z¦¡¡C
  5.         '1*****³B²z­n§R°£ªº¦C ªº°j°é****************
  6.         For Each E In .UsedRange.Columns("A:C").Rows
  7.              'UsedRange ÄÝ©Ê·|¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí«ü©w¤u§@ªí¤Wªº¤w¥Î½d³ò
  8.             If (Not IsDate(E.Cells(1)) And E.Cells(1) <> "") Or E.Cells(2) <> "" Or Application.CountA(E.Cells) = 0 Then
  9.                 If Rng Is Nothing Then   'Nothing ÃöÁä¦r¬O¥Î¨Ó±N¤@­Óª«¥óÅܼƱq¤@­Ó¹ê»Úªºª«¥óùؤÀÂ÷¶}¨Ó
  10.                    Set Rng = E   'Set(³]©wÅܼƬ°ª«¥ó)  ­n§R°£ªº¦C(ª«¥ó)«ü©w¨ìÅܼÆ
  11.                 Else    'Not Nothing
  12.                     Set Rng = Union(Rng, E)  'Application.Union ¤èªk ·|¶Ç¦^¨â­Ó¥H¤W½d³òªºÁp¶°¡C
  13.                 End If
  14.             End If
  15.         Next
  16.         If Not Rng Is Nothing Then Rng.EntireRow.Delete   '
  17.             'Range.EntireRow ÄÝ©Ê·|¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí¥]§t«ü©w½d³òªº¾ã­Ó¦C (©Î­Y¤z¦C)¡C
  18.         '2***********AÄ榳ªÅ¥Õ¶ñ¤W¤é´Á   *******************************
  19.         For Each E In Range("a1:a" & Cells(Rows.Count, 2).End(xlUp).Row)
  20.             If E(1, 3) <> "" And E = "" Then E = E.Cells(0)
  21.         Next
  22.         '3********«ü©w­n½Æ»sªº½d³ò*********************************
  23.        Set Rng = .UsedRange   'Sheets("vba")¤W¤w¨Ï¥Îªº½d³ò¬° "A:H"
  24.     End With
  25.     '4**************«ü©wªº½d³ò½Æ»s¨ì«ü©w¦ì¸m **********************
  26.     With Sheets("¸ê®Æ®w").Range("b1").End(xlDown).Offset(1)
  27.         'Range.End ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥]§t¨Ó·½½d³ò¤§°Ï°ìµ²§À³BªºÀx¦s®æ¡C
  28.         'µ¥©ó«ö END+¦V¤WÁä¡BEND+¦V¤UÁä¡BEND+¦V¥ªÁä©Î END+¦V¥kÁä
  29.    
  30.         'Range.Offset ÄÝ©Ê ·|¶Ç¦^ Range ª«¥ó¡A¥Nªí±q«ü©w¤§½d³ò¦ì²¾ªº½d³ò
  31.         Rng.Copy .Cells
  32.     End With
  33. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 15# GBKEE
Dear ª©¤j±z¦n,
«Ü©êºp¤@ª½½Ð±Ð±z°ÝÃD¡C
±z¥i¥HÀ°§Ú³æ¿W¼g¤@­Ó(§Ú·Q¥ý¾Ç²ß³o¤@³¡¤À)

¸ê®Æcopy¨ì¥t¤@¤u§@ªí(³o¤u§@ªíªº¸ê®Æ¬O«ùÄò¦³¸ê®Æ²Ö­pªº¡A¸ê®Æ¥u·|Åܧó¦h)
­n«ç»ò¥ÎVBA¼g¦Ü¸Ó¤u§@ªí
¤u§@ªí"VBA"±qAÄæ¨ìHÄæ­nCOPY¨ì¤u§@ªí"¸ê®Æ®w"¤¤
*­ì¤u§@ªí"¸ê®Æ®w"¤¤ªº¸ê®Æ¤´­n«O¯d

Àɮצpªþ¥ó

TT-2.rar (140.07 KB)
Just do it.

TOP

¦^´_ 15# GBKEE
Dear ª©¥D,
¤F¸Ñ¤F¡A­è­è¥J²Ó¬Ý¤FVBA»yªk¤j·§ª¾¹D­þ¤@¬q¬O¦b°µ¬Æ»ò°Ê§@¤F¡A·PÁ±zªº¸Ô²Ó¸Ñ»¡¡I
Just do it.

TOP

¦^´_ 16# jsc0518
Dear ª©¥D±z¦n¡A
§Ú±NVBA¿@ÁY¦¨¤U¦C»¡©ú¡A·Q¸ò±z½Ð±Ð¤@¤U
1.¦b¤u§@ªí"¸ê®Æ®w"BÄæ~IÄd¤U­Y¦³¾ú¥v¸ê®Æ®É¡AVBA¥i¥HCOPY¹L¥h
2.¦b¤u§@ªí"¸ê®Æ®w"BÄæ~IÄd¤U­YµL¾ú¥v¸ê®Æ®É¡A·|µo¥Í¿ù»~

Noname.jpg
2018-2-8 13:28


ÀÉ®×
TT.rar (78.22 KB)

VBA »yªk

Option Explicit
Sub Step1()
    Dim E As Range, Rng As Range
    With Sheets("vba")  'With ³¯­z¦¡ ¦b¤@­Ó³æ¤@ª«¥ó©Î¤@­Ó¨Ï¥ÎªÌ¦Û­q«¬ºA¤W°õ¦æ¤@¨t¦Cªº³¯­z¦¡¡C
        '3********«ü©w­n½Æ»sªº½d³ò*********************************
       Set Rng = .UsedRange   'Sheets("vba")¤W¤w¨Ï¥Îªº½d³ò¬° "A:H"
    End With
    '4**************«ü©wªº½d³ò½Æ»s¨ì«ü©w¦ì¸m **********************
    With Sheets("¸ê®Æ®w").Range("b1").End(xlDown).Offset(1)
        'Range.End ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥]§t¨Ó·½½d³ò¤§°Ï°ìµ²§À³BªºÀx¦s®æ¡C
        'µ¥©ó«ö END+¦V¤WÁä¡BEND+¦V¤UÁä¡BEND+¦V¥ªÁä©Î END+¦V¥kÁä
   
        'Range.Offset ÄÝ©Ê ·|¶Ç¦^ Range ª«¥ó¡A¥Nªí±q«ü©w¤§½d³ò¦ì²¾ªº½d³ò
        Rng.Copy .Cells
    End With
End Sub
Just do it.

TOP

¦^´_ 18# jsc0518
¥i§ï¤@¤U
  1. With Sheets("¸ê®Æ®w").Range("b" & Rows.Count).End(xlUp)
  2.         If .Cells = "" Then    '²Ä¤@¦C
  3.             Rng.Copy .Cells
  4.         Else
  5.             Rng.Copy .Offset(1)
  6.         End If
  7.     End With
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 19# GBKEE
Dear ª©¥D,
·PÁ¦^´_¡A¥i¥H¥Î¤F¡A·P®¦¡I
Just do it.

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD