[µo°Ý] ¦p¦ó¯à§ä¨ì³Ì«á¤@¦C¦³¸ê®Æªº¦C¼Æ¡H(©¿²¤ªÅ®æ)

¤p«L«È

¤j®a¦n¡A§Ú¦b³B²z¸ê®Æ®É¹J¨ì¤@­Ó¤p°ÝÃD¡A§Æ±æ¤j®a¥i¥HÀ°§U¸Ñ¨M¡C
§Ú¦³¤@­ÓEXCEL TEMPLATE¡A·í¤¤¦³«Ü¦hSHEETS¡A¬Oµ¹¨ä¥L¦P¨Æ¶ñ¼g¦U¤è­±ªº¸ê®Æ¡C
³]­p¤j·§¬O¡A­º¨â¦C¬OHEADING¡A¤U­±¬O¾­û¶ñ¼gªº¸ê®Æ¡C
¦pªG¨º¤@±iSHEET¨S¶ñ¤W¸ê®Æ¡A§Ú«K·|§â¨ºSHEET§R±¼¡C©Ò¥H§Ú¼g¤F¤@­ÓVBA
¦pªGLAST ROWªº¼Æ¦r<3¡A§Ú«K§â¨º­¶§R¥h¡C


§ä¥X¤u§@ªíªº³Ì«á¤@¦Cªº¦ì¸m¡A§Ú¬O¥Î³o¥y¡G
Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
®ÄªGÁٺ⤣¿ù¡A¦ý§Úµo²{¦³Â¾­û¦b¬Y¨Ç¦a¤è¥´¤WªÅ®æ¡Aµ²ªG¥Oµ{¦¡»~§P¡C

½Ð°Ý¦³¬Æ»ò¤èªk¥i¥H©¿²¤ªÅ®æªº¼vÅT¡A§ä¥X³Ì«á¤@¦C¦³¸ê®Æªº¦C¼Æ¡HÁÂÁ¡C

loquat

¥t¤À¨É¤@¨Çªð¦^³Ì¦Z¤@个«DªÅ单¤¸®æªº¬Û关应¥Î

ªð¦^³Ì¦Z¤@个«DªÅ单¤¸®æªº内®e¡G
=lookup(2,1/(A1:A10<>""),A1:A10) '
=lookup(9E+307,M5:M23,M5:M23)    '©¿²¤¤å¥»
=lookup(9E+307,M5:M23)           '©¿²¤¤å¥»

A1:A20¦s©ñ着数¦r¡B¤å¥»¡B错误­Èµ¥
=LOOKUP(9E+307,A1:A20)                        ªð¦^数­È
=LOOKUP(9E+307,A1:A20,ROW(A1:A20))        ªð¦^¦æ号
=LOOKUP(2,1/(A1:A20<>""),A1:A20)        ªð¦^«DªÅ单¤¸®æ
=LOOKUP(2,1/(A1:A20<>""),ROW(A1:A20))        ªð¦^¦æ号
=LOOKUP(2,1/(A1:A20<>0),A1:A20)                ªð¦^«D¹s单¤¸®æ
=LOOKUP(2,1/(A1:A20<>0),ROW(A1:A20))        ªð¦^¦æ号
=LOOKUP(2,1/(A1:A20="a"),A1:A20)        ªð¦^«ü©w¤å¥»单¤¸®æ
=LOOKUP(2,1/(A1:A20="a"),ROW(A1:A20))        ªð¦^¦æ号
=LOOKUP(2,1/(1-ISBLANK(A1:A20)),A1:A20)        ªð¦^«DªÅ单¤¸®æ
=LOOKUP(2,1/(1-ISBLANK(A1:A20)),ROW(A1:A20))        ªð¦^¦æ号
=LOOKUP(2,1/((A1:A20<>0)*ISNUMBER(A1:A20)),A1:A20)        ªð¦^¤£为¹s«DªÅ单¤¸®æ
=LOOKUP(2,1/((A1:A20<>0)*ISNUMBER(A1:A20)),ROW(A1:A20))        ªð¦^¦æ号
=VLOOKUP(9E+307,A1:A20,1,1)                ªð¦^³Ì¤j数­È
=VLOOKUP(REPT("ï¶",255),A1:A20,1,1)        ªð¦^³Ì¤j¤å¥»
=INDEX(A1:A20,,MATCH("*",A1:A20,-1))        ªð¦^¥ô·N­È
=INDEX(A1:A20,MAX(IF(A1:A20<>"",ROW(A1:A20))))        ªð¦^«DªÅ单¤¸®æ
ªð¦^³Ì¦Zªº¡§¦æ号¡¨
----«DªÅ­È(¤èªk¤@)        
LOOKUP(2,1/(A3:A65536<>""),row(A3:A65536))
----«DªÅ­È(¤èªk¤G)        
LOOKUP(2,1/(1-ISBLANK(A3:A65536)),row(A3:A65536))
----«DªÅ­È(¤èªk¤T  数组¤½¦¡)        
MAX((A3:A65536<>"")*ROW(A3:A65536))
----数­È(¤èªk¤@)        
LOOKUP(9E+307,A:A)
----数­È(¤èªk¤G)        
MATCH(9E+307,A:A)
----¤å¥»(¤èªk¤@)        
LOOKUP("®y",row(A:A))
----¤å¥»(¤èªk¤G)      
MATCH("®y",A:A)

loquat

ªì学VBA¡A写ªº东¦è¨Ñ±z参¦Ò

1. Sub Example()
   
2. Application.ScreenUpdating = False
   
3. Application.EnableEvents = False
   
4. Dim r As Range, i As Integer, j As Integer, temp As Integer
   
5. Dim c As Integer, str As String
   
6. j = 10   'HEADING 总¦@¦³10页
   
7. temp = 0 'ªì©l¤Æ数Õu
   
8. c = Rows.Count
   
9. For i = 1 To Worksheet.Count
   
10.     str = Worksheet(i).Name
    
11.     For Each r In Worksheet(i)
    
12.         If Trim(r.Value) = "" Then
    
13.         r.ClearContents
    
14.         End If
    
15.     Next r
    
16.     For i = 1 To j
    
17.         temp = temp + Cells(c, i).End(xlUp).Row - 2
    
18.     Next i
    
19.     If temp = 0 Then
    
20.         Worksheet(str).Delete
    
21.     End If
    
22. Next i
    
23. Application.EnableEvents = True
    
24. Application.ScreenUpdating = True
    
25. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 1# ¤p«L«È

Set c = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Do While Not c Is Nothing
    If Trim(c.Value) = "" Then
        c.ClearContents  '¬Ý§A­n¤£­n²M°£±¼¸ÓªÅ¥Õ¦r¤¸
        Set c = Cells.FindPrevious(c)
    Else
        lastRow = c.Row
        Exit Do
    End If
Loop