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

jsc0518

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

GBKEE

¦^´_ 25# jsc0518
¤@­Óµ{¦¡½X¨Ã¤£¯à³q¥Î©ó©Ò¦³Ãþ¦üªºª¬ªp
ªþÀɤ¤¦]AÄæ¨S¦³¨Ï¥Î¨ì ,¤£¾A¥Î ActiveSheet.UsedRange.Columns("B") ¬° ActiveSheetªº C Äæ
­n¬Ý§A¹ê»Ú¤WÀɮ׻ݦ³©Ò­×§ï,½Ð¦AªþÀɬݬÝ

jsc0518

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

¤U¸ü¦¸¼Æ: 4

2018-2-12 16:39

jsc0518

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

jsc0518

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

GBKEE

¦^´_ 21# jsc0518
Sub Step1() , Sheets("vba") ,  Sheets("¸ê®Æ®w")
¬O¦b¦P¤@­Ó xlsm  ¶Ü?
¤£¬O ¶·«ü¦W Workbooks("xxx").( Sheets("vba") ,  Workbooks("xxx"). Sheets("¸ê®Æ®w")

jsc0518

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

¤U¸ü (6.28 KB)

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

jsc0518

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

GBKEE

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

jsc0518

¦^´_ 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¥Í¿ù»~

¤U¸ü (103.99 KB)

2018-2-8 13:28

ÀÉ®×
TT.rar (78.22 KB)

¤U¸ü¦¸¼Æ: 2

2018-2-8 13:29

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