VBA/Excel/Access/Word/Access/UsedRange

Материал из VB Эксперт
Перейти к: навигация, поиск

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>