http://www.vbex.ru/index.php?action=history&feed=atom&title=VBA%2FExcel%2FAccess%2FWord%2FAccess%2FUsedRange
VBA/Excel/Access/Word/Access/UsedRange - История изменений
2026-04-07T04:05:32Z
История изменений этой страницы в вики
MediaWiki 1.30.0
http://www.vbex.ru/index.php?title=VBA/Excel/Access/Word/Access/UsedRange&diff=1398&oldid=prev
в 16:33, 26 мая 2010
2010-05-26T16:33:00Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr style="vertical-align: top;" lang="ru">
<td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td>
<td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 16:33, 26 мая 2010</td>
</tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div>
</td></tr></table>
http://www.vbex.ru/index.php?title=VBA/Excel/Access/Word/Access/UsedRange&diff=1399&oldid=prev
Admin: 1 версия
2010-05-26T12:46:59Z
<p>1 версия</p>
<p><b>Новая страница</b></p><div>==Loop through all used range==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="vb"><br />
<br />
Sub ResetTest4()<br />
For Each n In ActiveSheet.UsedRange<br />
If n.Value <> 0 Then<br />
n.Value = 0<br />
End If<br />
Next n<br />
End Sub<br />
</source><br />
<br />
<br />
<br />
<br />
==Remove extraneous rows and columns and reset the last cell==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="vb"><br />
<br />
Sub DeleteUnusedFormats()<br />
Dim lLastRow As Long, lLastColumn As Long<br />
Dim lRealLastRow As Long, lRealLastColumn As Long<br />
With Range("A1").SpecialCells(xlCellTypeLastCell)<br />
lLastRow = .Row<br />
lLastColumn = .Column<br />
End With<br />
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas,, xlByRows, xlPrevious).Row<br />
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas,, _<br />
xlByColumns, xlPrevious).Column<br />
If lRealLastRow < lLastRow Then<br />
Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete<br />
End If<br />
If lRealLastColumn < lLastColumn Then<br />
Range(Cells(1, lRealLastColumn + 1), _<br />
Cells(1, lLastColumn)).EntireColumn.Delete<br />
End If<br />
ActiveSheet.UsedRange "Resets LastCell<br />
End Sub<br />
</source><br />
<br />
<br />
<br />
<br />
==Using the Areas Collection to Return a Noncontiguous Range==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="vb"><br />
<br />
Sub copy()<br />
range("A:D").SpecialCells(xlCellTypeConstants, 1).copy range("I1")<br />
End Sub<br />
</source><br />
<br />
<br />
<br />
<br />
==Using the SpecialCells method to select all the blanks in this range is one way to quickly fill in all the blank region cells with the region found above them:==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="vb"><br />
<br />
Sub FillIn()<br />
On Error Resume Next<br />
range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"<br />
range("A1").CurrentRegion.value = range("A1").CurrentRegion.value<br />
End Sub<br />
</source><br />
<br />
<br />
<br />
<br />
==Working with the Used Range==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="vb"><br />
<br />
Sub auto()<br />
ActiveSheet.UsedRange.Columns.AutoFit<br />
End Sub<br />
<br />
</source></div>
Admin