[µo°Ý] VBA§R°£ªÅ¥ÕROW COLUMN

missbb

«öCTRL ¤U½b©C, ¹C¼Ð¥h¨ìAK170, ¦ý¨ä¦³¸ê®Æªº¦æ/¦C¥u¥h¨ìE150. ¦p¦ó¥i¥ÎVBA§Ö°£°£¥h151¦Ü170ªºªÅ¥ÕROW¤ÎF¦ÜAKªºªÅ¥ÕÄæ? ¦]·|¥OÀÉ®×®e¶q«Ü¤j.

¦³³Ò¦U¦ì, ¦]¦bºô°^¬Ý¤©«Ü¦h¤å³¹®¯°µ¤£¨ì.:'(

°£¥hªÅ¥ÕROW.zip (19.06 KB)

¤U¸ü¦¸¼Æ: 15

2018-5-30 19:14

missbb

¦^´_ 8# Kubi

«D±`·PÁÂ:)

Kubi

²×©ó¤F¸Ñ¨ì­ì¨Ó Delete ­Y¨S¦³¥[¤W°Ñ¼Æ Shift¡AVBA ·|¦Û°Ê§PÂ_§R°£¤§«áªºÀx¦s®æ¦ì²¾¤è¦V¡A
©Ò¥H­Y¥[¤W°Ñ¼Æ Shift «á¡A´N·|»P EntireRow.Delete¡BEntireColumn.Delete ²£¥Í¬Û¦P®ÄªG¡C

­ã´£³¡ªL

Sub TEST()
Dim UR As Range, xF As Range
Set UR = Range([A1], ActiveSheet.UsedRange)
Set UR = UR(UR.Count)

Set xF = Cells.Find("*", UR(2), xlValues, xlPart, xlByRows, xlPrevious)
If UR.Row > xF.Row Then Set UR = UR(2): Range(xF(2), UR(0)).EntireRow.Delete

Set xF = Cells.Find("*", UR(1, 2), xlValues, xlPart, xlByColumns, xlPrevious)
If UR.Column > xF.Column Then Range(xF(1, 2), UR).EntireColumn.Delete
ActiveSheet.UsedRange
End Sub

Kubi

¦^´_ 5# GBKEE
°õ¦æGBKEEª©¥Dªº³Ì«á¤@¦æ Debug.Print ActiveSheet.UsedRange.Address «á
Åã¥Üªº¬O $A$1:$AK$150

­Y¯à°õ¦æ¨ì³Ì«á¤@¦æ Debug.Print ActiveSheet.UsedRange.Address «á
Åã¥Üªº¬O $A$1:$E$150

¥i¯à´N¬O¼Ó¥D©Ò­nªºµ²ªG

GBKEE

¦^´_ 4# Kubi
¸É¤W§¹¾ãµ{¦¡«á
UsedRange·|¬O¦b³Ì«áÀx¦s®æ¦³¸ê®Æªº½d³ò¤º

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim R(1 To 2), C(1 To 2) As Long, Rng As Range
   
4.     Debug.Print  ActiveSheet.UsedRange.Address
   
5.     If Application.CountA(Cells) = 0 Then Exit Sub
   
6.     Set Rng = Cells.SpecialCells(xlCellTypeConstants)
   
7.     Set Rng = Rng.Areas(Rng.Areas.Count)
   
8.     Set Rng = Rng.Cells(Rng.Cells.Count)
   
9.     With Rng
   
10.         R(1) = .Row
    
11.         C(1) = .Column
    
12.     End With
    
13.     With Cells.SpecialCells(xlCellTypeLastCell)
    
14.         R(2) = .Row
    
15.         C(2) = .Column
    
16.     End With
    
17.     If R(1) <> R(2) Then Rows(R(1) + 1 & ":" & R(2)).Delete
    
18.     If C(1) <> C(2) And C(1) + 1 = C(2) Then Range(Columns(C(1) + 1), Columns(C(2))).Delete
    
19.     ActiveSheet.Parent.Save
    
20.     Debug.Print  ActiveSheet.UsedRange.Address
    
21. End Sub

½Æ»s¥N½X

Kubi

¦^´_ 3# GBKEE
¼Ó¥Dªº¥Î·N¬O»¡«ö {END} Áä«á¦A«ö {HOME} Áä¡A·|±Nªo¼Ð²¾¦Ü
Cells.SpecialCells(xlCellTypeLastCell).Select ¦ì¸m¡A¤]´N¬OAK170Àx¦s®æ¡C

¤£¶È¶È¸ÓÀx¦s®æ¬OªÅ­È¤§¥~¡A©óAK170Àx¦s®æ©¹¥ª(F:AKÄæ)©Î©¹¤W(151:170¦C)ªº½d³ò¤ºÀx¦s®æ³£¬OªÅ­È¡C

¼Ó¥D¬O·Q»¡§Q¥Îµ{¦¡±NµêÀÀªºªÅÀx¦s®æ¥h°£¡A¥H«á¦A«ö {END} Áä¦A«ö {HOME} Áä¡A´å¼Ð·|²¾¦Ü¯u¥¿¦³¸ê®Æªº¥k¤U¨¤Àx¦s®æE150¡A¦Ó¤£¬OAK170¡C
¦Ó¥B­Y¤£±N³o¨ÇªÅªºÀx¦s®æ²M°£©Î§R°£¡A±N·|¦û¥Î°O¾ÐÅé¤]·|ÅýÀɮתΤj¡C

·|³y¦¨xlCellTypeLastCell»~§P¡A¥i¯à­ì¦]¬O½Æ»s§t¦³®æ¦¡ªº¸ê®Æ(¦pCopyºô­¶¤ºªº¸ê®Æ)¶K¤J¤u§@ªí¡A
¤§«á­Y¸Ó¶K¤Jªº¸ê®Æ¥Î¤£¤W®É¦A¥ÎDeleletÁä§R°£¡A´N·|²£¥Í¦p¤W­±ªº»~§P²{¶H¡C

¥t¥~¥ÎRangeªºUsedRangeÄݩʤ]·|²[»\A1:AK170½d³ò¡C

¥H¤W¬O­Ó¤HÆ[¹îµ²ªG¨Ñ°Ñ¡C

GBKEE

¦^´_ 1# missbb
¬O³o¼Ë¶Ü?
****³Ì«á¦³¸ê®Æªº¦C¦ì,¨ä¹ê¤£¬O³Ì«á¦³¨Ï¥Î¹Lªº¦C¦ì***(UsedRange)

1. Option Explicit
   
2. Sub Ex()
   
3.     If Cells.SpecialCells(xlCellTypeLastCell).Row <> [E1].End(xlDown).Row Then
   
4.         Rows([E1].End(xlDown).Row + 1 & ":" & Cells.SpecialCells(xlCellTypeLastCell).Row).Delete
   
5.     End If
   
6. End Sub

½Æ»s¥N½X

Kubi

¦^´_ 1# missbb
¥H¤U¬O¤gªk·Ò¿ûªºµ²ªG¡G

1. Sub test()
   
2.     For n = 1 To 2
   
3.         er = 0
   
4.         ec = 0
   
5.         With ActiveSheet
   
6.             For j = 1 To .UsedRange.Columns.Count
   
7.                 For i = 1 To .UsedRange.Rows.Count
   
8.                     If .Cells(i, j).Value <> "" Then
   
9.                         If i > er Then er = i
   
10.                         If j > ec Then ec = j
    
11.                     End If
    
12.                 Next i
    
13.             Next j
    
14.             .Rows(er + 1 & ":" & Cells.Rows.Count).Delete
    
15.             .Range(Columns(ec + 1), Columns(Cells.Columns.Count)).Delete
    
16.         End With
    
17.     Next n
    
18. End Sub

½Æ»s¥N½X

§O°Ý§Ú¬°¦ó­n°j圏¨â¦¸¡H³o¬O¦h¤è´ú¸Õµ²ªG(ÁÙ¬O¦³«e½úª¾¹D­ì¦]¨Ã§iª¾)¡A­Y¤£¥Î°j°éªº¸Ü¡A«h³Ì«á¤@¦C­n¥[¤Wµ{¦¡½X¨ÓÃö³¬ÀɮסG
ActiveWorkbook.Close 1

ÃöÀɮתº¥Î·N¬O¸g¹L¦A¶}±Ò¥»Àɮתºµ{§Ç¤~·|¥Í®Ä¡C