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

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

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

«ö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)

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

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-6-2 17:57 ½s¿è

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

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

TOP

²×©ó¤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
[b]Kubi[/b]

TOP

¦^´_ 8# Kubi

«D±`·PÁÂ:)

TOP

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