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

[µo°Ý] ½Ð°Ý­n¦p¦ó«ü©w§R°£¸ê®Æ

[µo°Ý] ½Ð°Ý­n¦p¦ó«ü©w§R°£¸ê®Æ

¦U¦ì¾Çªø¦n:
§Ú¦bºô¸ô¤W¦¬´M¨ì¤@¬qµ{¦¡¡AÁöµM«Ü¦n¥Î
¦ý¨C¤é¸ê®Æ®w¦³¤W¸Uµ§¸ê®Æ¡A³y¦¨¨C¤@¶µÀˬd+§R°£ªá¤F«Ü¤[®É¶¡
¦³¿ìªk¦b²¤ÆÀˬdªº®É¶¡¶Ü?¹w­p¶}¤u¤é¤w¦³¨Ì·Ó®É¶¡±Æ§Ç

    Set sh1 = Sheets(1) '§R°£¶}¤u¤é¶W¹L¤µ¤Ñ
    f = sh1.Columns("A:L").Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
    On Error Resume Next
    dn = Date + 1
    For i = f To 2 Step -1
    If IsError(sh1.Cells(i, "L")) = False Or sh1.Cells(i, "L") <> "" Then
    dv2 = sh1.Cells(i, "K")
    y2 = Format(Left(dv2, 4), "0000")
    m2 = Format(Mid(dv2, 5, 2), "00")
    d2 = Format(Mid(dv2, 7, 2), "00")
    dv2 = DateValue(Format(y2 & "/" & m2 & "/" & d2, "yyyy/mm/dd"))
    If dv2 > dn Then
    sh1.Rows(i).Delete Shift:=xlUp
    End If
    End If
    Next

¬¡­¶Ã¯2.rar (14.21 KB)

¥»©«³Ì«á¥Ñ samwang ©ó 2021-12-27 16:53 ½s¿è
¦U¦ì¾Çªø¦n:
§Ú¦bºô¸ô¤W¦¬´M¨ì¤@¬qµ{¦¡¡AÁöµM«Ü¦n¥Î
¦ý¨C¤é¸ê®Æ®w¦³¤W¸Uµ§¸ê®Æ¡A³y¦¨¨C¤@¶µÀˬd+§R°£ªá¤F ...
cclo0728 µoªí©ó 2021-12-27 16:08


±zªº»Ý¨D¬O§R°£¤j©ó¤µ¤é¹w­p¶}¤u(¥À)¶Ü? ½Ð½T»{¡AÁÂÁÂ

TOP

¦^´_ 1# cclo0728
½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, SD As Date, y2, m2, d2, i&, j%, n%
Application.ScreenUpdating = False
Tm = Timer
With Range("a1").CurrentRegion
    Arr = .Value
    For i = 2 To UBound(Arr)
        y2 = Format(Left(Arr(i, 11), 4), "0000")
        m2 = Format(Mid(Arr(i, 11), 5, 2), "00")
        d2 = Format(Mid(Arr(i, 11), 7, 2), "00")
        SD = DateSerial(y2, m2, d2)
        If SD < Date Then
            n = n + 1
            For j = 1 To UBound(Arr, 2): Arr(n, j) = Arr(i, j): Next
        End If
    Next
    If n > 0 Then
        .Range("a1").CurrentRegion.Offset(1) = ""
        .Range("a2").Resize(n, UBound(Arr, 2)) = Arr
    End If
End With
Application.ScreenUpdating = True
MsgBox Timer - Tm
End Sub

TOP

±zªº»Ý¨D¬O§R°£¤j©ó¤µ¤é¹w­p¶}¤u(¥À)¶Ü? ½Ð½T»{¡AÁÂÁÂ
samwang µoªí©ó 2021-12-27 16:48

¬Oªº¡A¦ý­n¦h¥[¤@¤Ñ¡A¦]¬°µo®Æ­n´£¦­¤@¨B¡A©Ò¥H¤µ¤Ñ¬O28¸¹ªº¸Ü¡A¤é´Á³£­n+1

TOP

¦^´_  cclo0728
½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, SD As Date, y2, m2, d2, i&, j%, n%
Applicat ...
samwang µoªí©ó 2021-12-28 07:48


·PÁ¦^ÂСA§Úµ¥µ¥´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

TOP

¬Oªº¡A¦ý­n¦h¥[¤@¤Ñ¡A¦]¬°µo®Æ­n´£¦­¤@¨B¡A©Ò¥H¤µ¤Ñ¬O28¸¹ªº¸Ü¡A¤é´Á³£­n+1
cclo0728 µoªí©ó 2021-12-28 08:24


¬Oªº¡A¦ý­n¦h¥[¤@¤Ñ¡A¦]¬°µo®Æ­n´£¦­¤@¨B¡A©Ò¥H¤µ¤Ñ¬O28¸¹ªº¸Ü¡A¤é´Á³£­n+1
>> If SD <= Date Then
­×§ï¦p¬õ¦r¡AÁÂÁÂ

TOP

¬Oªº¡A¦ý­n¦h¥[¤@¤Ñ¡A¦]¬°µo®Æ­n´£¦­¤@¨B¡A©Ò¥H¤µ¤Ñ¬O28¸¹ªº¸Ü¡A¤é´Á³£­n+1
>> If SD
samwang µoªí©ó 2021-12-28 08:29


·PÁÂ~«D±`·PÁ§AªºÀ°¦£¡A´ú¸Õ¨Ï¥Î¨S°ÝÃD
¦Ó¥B³t«×«Ü§Ö¡A§Ú¨Ó¬ã¨s¤@¤U
ÁÂÁÂ

TOP

SD = Format(Arr(i, 11), "0-00-00")

©Î¥ý±N·í¤ÑÂন8¦ì¼Æ¦r
TD& = Format(Date, "YYYYMMDD")
If Val(Arr(i, 11)) <= TD Then

TOP

SD = Format(Arr(i, 11), "0-00-00")

©Î¥ý±N·í¤ÑÂন8¦ì¼Æ¦r
TD& = Format(Date, "YYYYMMDD")
If Val( ...
­ã´£³¡ªL µoªí©ó 2021-12-28 13:53


·PÁ­ã¤j´£¿ô¡A¾Ç²ß¤F¡AÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD