[¤À¨É] usedrange

wqfzqgk

Sub usedrange¦æ¦C()
Dim Col_Count As Long
With ActiveSheet.UsedRange
For Col_Count = .Columns.Count To 1 Step -1
If WorksheetFunction.CountA(.Item(1).Resize(1, .Columns.Count)(Col_Count).EntireColumn) = 0 Then
.Item(1).Resize(1, .Columns.Count)(Col_Count).EntireColumn.Delete
End If
Next
End With
Dim Row_Count As Long
With ActiveSheet.UsedRange
For Row_Count = .Rows.Count To 1 Step -1
If WorksheetFunction.CountA(.Item(1).Resize(.Rows.Count, 1)(Row_Count).EntireRow) = 0 Then
.Item(1).Resize(.Rows.Count, 1)(Row_Count).EntireRow.Delete
End If
Next
End With
With ActiveSheet.UsedRange
MsgBox "¤w¥Îªº区°ì¦a§}为¡G" & .Address & Chr(13) & "³Ì¤j¦æ为¡G" & .Rows.Count & Chr(13) & "³Ì¤j¦C为¡G" & .Columns.Count _
& Chr(13) & "¦@¦³单¤¸®æ数¡G" & .Cells.Count & Chr(13) & "²Ä¤@¦æ为¡G" & .Item(1, 1).Row & Chr(13) & "²Ä¤@¦C为¡G" & .Item(1, 1).Column

End With
End Sub

¤j®a¦A·Q·Q¡A还¦³¤°¤\§Ñ±¼ªº¡A¦]为usedrange«D±`¯S别

avel

¦³时­Ô¥ÎUSEDRANGE¦}¤£¯à¯u¥¿¤Ï应sheet¤¤ªº数Õu¡C

cai168

­è¶}©l¾Ç²ßvba¨ü±Ð¤F

wqfzqgk

当时¬O¦Ò虑¦p¬d区°ì¤¤单¤¸®æ为ªÅ¡A¦Ó¦³单¤¸®æ¦¡©Î¨ä¥L¼v响usedrangeªº¤è­±¡A©Ò¥H¦³¤F¤W­±ªº两个删°£¦æÉO¦Cªºµ{§Ç

wqfzqgk

当时¬O随·Q随写¡Awith ÉOend with³Ì¦Z¤]没¦³¦X¦}

GBKEE

¦^´_ 1# wqfzqgk
2003 ¬°¨Ò
ÁcÅ骩 A-IV ºÙ¬°Äæ  1- 65536 ºÙ¬°¦C
²Å骩 A-IV ºÙ¬°¦C  1- 65536 ºÙ¬°¦æ
²¤Æ¼Ó¥Dªº¤À¨Éµ{¦¡ ¨Ã§ï¬°ÁcÅé

1. Sub Usedrange¦æ¦C()
   
2.     Dim i%
   
3.     With ActiveSheet.UsedRange
   
4.         For i = .Columns.Count To 1 Step -1
   
5.             If Application.CountA(.Columns(i)) = 0 Then .Columns(i).Delete
   
6.         Next
   
7.         For i = .Rows.Count To 1 Step -1
   
8.             If Application.CountA(.Rows(i)) = 0 Then .Rows(i).Delete
   
9.         Next
   
10.         MsgBox "¤w¥Îªº°Ï°ì¦a§}¬°¡G" & .Address & Chr(13) & "³Ì¤j¦C¬° : " & .Cells(.Count).Row & Chr(13) & "³Ì¤jÄ欰 : " & .Cells(.Count).Column _
    
11.             & Chr(13) & "¦@¦³³æ¤¸®æ¼Æ¡G" & .Count & Chr(13) & "²Ä¤@¦C¬° : " & .Cells(1).Row & Chr(13) & "²Ä¤@Ä欰 : " & .Cells(1).Column
    
12.     End With
    
13. End Sub

½Æ»s¥N½X