VBA/Excel/Access/Word/Access/UsedRange

Loop through all used range

 
Sub ResetTest4()
    For Each n In ActiveSheet.UsedRange
        If n.Value <> 0 Then
            n.Value = 0
        End If
    Next n
End Sub

Remove extraneous rows and columns and reset the last cell

 
     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

Using the Areas Collection to Return a Noncontiguous Range

 
Sub copy()
    range("A:D").SpecialCells(xlCellTypeConstants, 1).copy range("I1")
End Sub

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:

 
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

Working with the Used Range

 
Sub auto()
    ActiveSheet.UsedRange.Columns.AutoFit
End Sub