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

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

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

Dear ¥ý¶i,

VBA»yªk¦p¤U¡A»yªk¤º¦³§â"ªÅ¥Õ"³£µ¹§R°£
§Ú·Q§â"­Y¹J¨ìAÄæ¦CªÅ¥Õ®É¡A¤£­n§R°£"¡A½Ð°Ý¬O­n­×¥¿­þ¤@¬q»yªk©O¡H

·PÁ¦U¦ì¡I

Sub Step1()
'§R°£ key word : ¤½¥q / ³æ§O: / ¤é´Á* / ²£«~¤jÃþ: / ®Ö­ã / ¦X­p* / ²Î­p*
Dim reg As Object, a, rng As Range, z&
a = Range("a1:a" & Cells(Rows.Count, 1).End(3).Row).Value
z = UBound(a)
Set rng = Rows(z + 1)
Set reg = CreateObject("vbscript.regexp")
With reg
    .Pattern = "ªÅ¥Õ|¤½¥q|³æ§O:|¤é´Á|²£«~¤jÃþ:|®Ö­ã|¦X­p|Á`­p"
    For i = 1 To z
        If Len(a(i, 1)) = 0 Or .test(a(i, 1)) Then
            Set rng = Union(rng, Rows(i))
        End If
    Next
End With
rng.Delete
Set rng = Nothing
End Sub
Just do it.

¦^´_ 25# jsc0518
¤@­Óµ{¦¡½X¨Ã¤£¯à³q¥Î©ó©Ò¦³Ãþ¦üªºª¬ªp
ªþÀɤ¤¦]AÄæ¨S¦³¨Ï¥Î¨ì ,¤£¾A¥Î ActiveSheet.UsedRange.Columns("B") ¬° ActiveSheetªº C Äæ
­n¬Ý§A¹ê»Ú¤WÀɮ׻ݦ³©Ò­×§ï,½Ð¦AªþÀɬݬÝ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 22# GBKEE
Àɮצp¤U
Book1.rar (11.12 KB)
Just do it.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2018/2/12 16:24): ªþÀɬݬÝ

¦^´_ 22# GBKEE
Dear ª©¥D,
§Ú¨Ï¥Î±zªºVBA°µ­×§ï"ªÅ¥ÕÄæ¦ì¥i¥H¦Û°Ê½Æ»s»P¤W¤@µ§¸ê®Æ¬Û¦P"
§Ú§âVBA§ï¦¨BÄæ®É¡A«oµL°Ê§@(ªÅ¥ÕÄæ¦ìµLªk¦Û°Ê½Æ»s»P¤W¤@µ§¸ê®Æ¬Û¦P)¡A¬O§_­þ¤@¬q»yªk¥¼­×­q¡H
Thanks!

Sub Step6()
'ªÅ¥ÕÄæ¦ì¥i¥H¦Û°Ê½Æ»s»P¤W¤@µ§¸ê®Æ¬Û¦P-B
With ActiveSheet.UsedRange.Columns("B")
        If .Cells(1).End(xlDown).End(xlDown).Row <> Rows.Count Then  '§PÂ_¦³ªÅ¥ÕªºÀx¦s®æ  !!!!
            .SpecialCells(xlCellTypeBlanks).Cells = "=R[-1]C"                        '¨S¦³ªÅ¥ÕªºÀx¦s®æ .SpecialCells(xlCellTypeBlanks) µ{¦¡·|¿ù»~
            .Value = .Value
        End If
   End With
End Sub
Just do it.

TOP

¦^´_ 22# GBKEE
Dear ª©¥D,
§Ú¦A¸Õ¸Õ¬Ý±z©Ò´£¨Ñªº¤èªk
·P®¦¡I
Just do it.

TOP

¦^´_ 21# jsc0518
Sub Step1() , Sheets("vba") ,  Sheets("¸ê®Æ®w")
¬O¦b¦P¤@­Ó xlsm  ¶Ü?
¤£¬O ¶·«ü¦W Workbooks("xxx").( Sheets("vba") ,  Workbooks("xxx"). Sheets("¸ê®Æ®w")
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 19# GBKEE
Dear ª©¥D,
§Ú¹B¥Î¤F±z©Ò±Ð¾Éªº»yªk¡A¦³µo¥Í¤U¹Ï¡A½Ð°Ý­n§ó§ï­þ¤@»yªk©O¡H
ÁÂÁ±z!


Noname.jpg
2018-2-12 09:59



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("b" & Rows.Count).End(xlUp)
        If .Cells = "" Then    '²Ä¤@¦C
            Rng.Copy .Cells
        Else
            Rng.Copy .Offset(1)
        End If
    End With
End Sub
Just do it.

TOP

¦^´_ 19# GBKEE
Dear ª©¥D,
·PÁ¦^´_¡A¥i¥H¥Î¤F¡A·P®¦¡I
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

¦^´_ 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

        À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