VBA/Excel/Access/Word/Access/UsedRange
Содержание
- 1 Loop through all used range
- 2 Remove extraneous rows and columns and reset the last cell
- 3 Using the Areas Collection to Return a Noncontiguous Range
- 4 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:
- 5 Working with the Used Range
Loop through all used range
<source lang="vb">
Sub ResetTest4()
For Each n In ActiveSheet.UsedRange If n.Value <> 0 Then n.Value = 0 End If Next n
End Sub
</source>
Remove extraneous rows and columns and reset the last cell
<source lang="vb"> Sub DeleteUnusedFormats() Dim lLastRow As Long, lLastColumn As Long Dim lRealLastRow As Long, lRealLastColumn As Long With Range("A1").SpecialCells(xlCellTypeLastCell) lLastRow = .Row lLastColumn = .Column End With lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas,, xlByRows, xlPrevious).Row lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas,, _ xlByColumns, xlPrevious).Column If lRealLastRow < lLastRow Then Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete End If If lRealLastColumn < lLastColumn Then Range(Cells(1, lRealLastColumn + 1), _ Cells(1, lLastColumn)).EntireColumn.Delete End If ActiveSheet.UsedRange "Resets LastCell End Sub </source>
Using the Areas Collection to Return a Noncontiguous Range
<source lang="vb">
Sub copy()
range("A:D").SpecialCells(xlCellTypeConstants, 1).copy range("I1")
End Sub
</source>
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:
<source lang="vb">
Sub FillIn()
On Error Resume Next range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" range("A1").CurrentRegion.value = range("A1").CurrentRegion.value
End Sub
</source>
Working with the Used Range
<source lang="vb">
Sub auto()
ActiveSheet.UsedRange.Columns.AutoFit
End Sub
</source>