EXCEL ¦Û°Ê¥[¤Jªí®æ

sschristy

½Ð°Ý¦U¦ì«e½ú¡A§Ú·Q­n§Q¥Î®Ö¨ú¤è¶ô±N»Ý­nªº¤º®e¤Ä¿ï¡A¦p¹Ï1¤¤²Ä3¡B4¦C¤Ä¿ï¡AÂI¿ï¥kÃ䪺"¥[¤J¥Dªí®æ"«ö¶s«á¡A
Åý³o¨â¦Cªí®æ¤ºªº¤å¦r¦Û°Ê¥[¨ì"¥Dªí®æ"¤¤¡A½Ð°Ý¬O§_¦³¿ìªk¹ê²{©O¡H

¤U¸ü (16.52 KB)

2023-10-2 15:31

¤U¸ü (16.79 KB)

2023-10-2 15:31

Andy2483

¦^´_ 20# sschristy


    ÁÂÁ«e½úÄ~Äò¤@°_¾Ç²ß
«á¾Ç«Øij¦p¤U:

With sh2.[B65536].End(3)(2).Resize(n, 6)
   .Value = Crr
   sh2.Activate
   .Select
   With .Cells(.Count + 1)
      .Value = Now: .Font.ColorIndex = 2
      '¡ô¥OBÄæ³Ì«á¥[¤@®æ¤º®e¬° ·í¤U®É¶¡ªºÀx¦s®æ,¥O¦r¦â¬°¥Õ¦â
      '¤]¥i¥H¥OÀx¦s®æ¤º®e¬°ªÅ¥Õ¦r¤¸.Value = " "
      '³o¼Ëªº¤è¦¡¤ñ¸û¤£·|¾É­P¿z¿ï¥\¯à¸ê®Æ¤£§¹¾ã(ªÅ¦C©ö¾É­P¸ê®Æ¤¤Â_)
   End With
End With

¤U¸ü (103.42 KB)

2023-12-25 09:55

sschristy

¦^´_ 10# Andy2483

ANDY¤j¤j¡A½Ð°Ý¦¹µ{¦¡½X¯à¤£¯à¼W¥[¤@¶µ¥\¯à¡H
´N¬O°²³]§Ú¿ï¤F¥|¦æ¦ý¥[¤J¨ì¥D­¶®É·|¦³¤­¦æ¡A¦Ó²Ä¤­¦æ¬°ªÅ¥Õ¦C¡C
³o¼Ëªº¥Î·N¦b¤ñ¸û¦n¤À¿ë¨C¤@¦¸¥[¤Jªº¤º®e¡C
ÁÂÁÂ

rtyfghvbn520

§Ú¬O¿n¤À¤£¨¬µLªkµo¤å
§V¤O¤¤

Andy2483

¦^´_ 16# sschristy


    ¥H¤U¥t¤@¤èªk,½Ð«e½ú°Ñ¦Ò
Private Sub CommandButton2_Click()
Dim xU As Range, xA As Range, xB As Range
Set xA = Selection.Cells: Set xB = ActiveSheet.UsedRange.Offset(3)
Set xU = Union(xA, xB)
If xU.Count <> xB.Count Then Exit Sub
Intersect(xA.EntireRow, [A:G]).Delete Shift:=xlUp
End Sub

Andy2483

¦^´_ 16# sschristy


    ¥H¤U¬O§å¦¸§R°£¿ï¨úªº¦C¸ê®Æ(¿ï¨ú®æ¦CÄd>«ö¶s) ©Î ¥þ³¡¦C¸ê®Æ§R°£(¾ãªí¿ï¨ú>«ö¶s)

Private Sub CommandButton2_Click()
Dim Q, A$, i&, n&, xI As Range, Brr, xA As Range, xB As Range
Set xA = Selection.Cells: Set xB = ActiveSheet.UsedRange
If xA.EntireRow.Count > xB.Rows.Count Then Set xA = Intersect(xA, xB)
If xA Is Nothing Then Exit Sub
Set xI = Intersect(Range([A1], xB), [A:G]): Brr = xI
For Each Q In Split(xA.EntireRow.Address(0, 0), ",")
   For i = Split(Q, ":")(0) To Split(Q, ":")(1)
      A = IIf(A = "", "/" & Val(i) & "/", A & Val(i) & "/")
   Next
Next
For i = 4 To xI.Rows.Count
   If InStr(A, "/" & i & "/") Then GoTo i01 Else n = n + 1
   For j = 1 To UBound(Brr, 2): Brr(n, j) = Brr(i, j): Next
i01: Next
xI.Offset(3, 0).ClearContents
If n = 0 Then Exit Sub
[A4].Resize(n, UBound(Brr, 2)) = Brr
End Sub

sschristy

¦^´_ 15# Andy2483


    ·PÁÂANDY¤j¤j¡A½Ð°Ý¦³¿ìªk¥u§å¦¸§R°£©Ò¿ïªºÄæ¦ì¶Ü¡H

Andy2483

¦^´_ 13# sschristy


    ¥H¤U¬O½Æ²ßªº¤ß±oµù¸Ñ,½Ð«e½ú°Ñ¦Ò

Private Sub CommandButton2_Click()
Dim xA As Range, xB As Range, xI As Range
'¡ô«Å§i(xA,xB,xI)¦U¬OÀx¦s®æÅܼÆ
Set xA = Range([A1], ActiveSheet.UsedRange)
'¡ô¥OxA³oÀx¦s®æÅܼƬO²[»\[A1]Àx¦s®æ»P¦¹ªí¤w¨Ï¥ÎÀx¦s®æ,
'²[»\³o°Ï¶¡ªº³Ì¤p¤è¥¿½d³òÀx¦s®æ
Set xB = [A:G]
'¡ô¥OxB³oÀx¦s®æÅܼƬO[A:G]Àx¦s®æ
Set xI = Intersect(xA, xB)
'¡ô¥OxI³oÀx¦s®æÅܼƬO xA»PxBÅܼƥ涰ªºÀx¦s®æ
xI.Offset(3, 0).ClearContents
'¡ô¥OxIÅܼƩ¹¤U°¾²¾3¦Cªº³o·s½d³òÀx¦s®æ²M°£¨ä¤º®e
End Sub

Andy2483

¦^´_ 13# sschristy


Private Sub CommandButton2_Click()
Dim xA As Range, xB As Range, xI As Range
Set xA = Range([A1], ActiveSheet.UsedRange)
Set xB = [A:G]
Set xI = Intersect(xA, xB)
xI.Offset(3, 0).ClearContents
End Sub

https://learn.microsoft.com/zh-t ... worksheet.usedrange
https://learn.microsoft.com/zh-t ... plication.intersect
https://learn.microsoft.com/zh-t ... /excel.range.offset

sschristy

¦^´_ 10# Andy2483


    ·PÁÂANDY¤j¤j¡A¨º½Ð°Ý²M°£¶µ¥Ø¬O§_¤]¥i¥H°µ¨ì¦h¦C¤@¦¸²M°£¡H

¤U¸ü (10.78 KB)

2023-10-23 16:54

¥H¤U¬O¥Ø«eªºµ{¦¡½X¡A¥u¯à¤@¦¸§R¤@¦C
Private Sub CommandButton2_Click()
Dim b As Integer
b = (ActiveCell.Row)
Range("A" & b & ":G" & b).ClearContents
End Sub