VBA/Excel/Access/Word/Excel/Range Value

Change cell A1 and move to cell A2 and change it

   <source lang="vb">

    Sub MyMacro()
        Range("A1").Value = "This is A1"
        Range("A2").Select
        ActiveCell.Value = "This is A2"
    End Sub
</source>
   
  

Clear NonDate Cells

   <source lang="vb">

Public Sub ClearNonDateCells()

 Dim aRange As Range
 For Each aRange In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
   If Not IsDate(aRange.Value) Then aRange.ClearContents
 Next aRange

End Sub

</source>
   
  

Reset value in a range to zero

   <source lang="vb">

Sub ResetValuesToZero2()

   For Each n In Worksheets("Sheet1").Range("WorkArea1")    "   Substitute your information here
       If n.Value <> 0 Then
           n.Value = 0
       End If
   Next n

End Sub

</source>
   
  

Return cell value in a range as an array

   <source lang="vb">

Sub Array3()

 Dim Data As Variant
 Data = Range("A1:A20").Value
 MsgBox "Number of Non Blank Elements = " & WorksheetFunction.CountA(Data) & vbCr

End Sub

</source>
   
  

Sum the range

   <source lang="vb">

Sub addAmtAbs()

   Set myRange = Range(ActiveCell, ActiveCell.End(xlUp))   "   Substitute your range here
   Dim myCount As Integer
   myCount = Application.Count(myRange)
   ActiveCell.Formula = "=SUM(B1:B" & myCount & ")"  "   Substitute your cell address here

End Sub

</source>
   
  

Transferring one-dimensional arrays with build-in function

   <source lang="vb">

Sub trans()

   range("A1:A3").value = Application.WorksheetFunction.Transpose(range("A1:C1"))

End Sub

</source>