Excel Macros - Page 1

 

  Iterations

___________________________________________________

' Insert_Rows Macro

' Macro recorded 5/7/97 by John Voris

‘ A great little macro to prompt you for EXACTLY the number of rows you wish to add.

Sub Insert_Rows()

count_rows = 1

'MSGBOX = input a valid number of rows to insert

Message = "Enter number of rows to insert" ' prompt.

Title = "Insert Multiple Rows" ' Set title.

Default = "1" ' Set default.

' Display message, title, and default value.

count_rows_a = InputBox(Message, Title, Default)

'valid number ?

count_rows = Val(count_rows_a)

On Error GoTo end_sub_Insert_Rows

If count_rows > 0 Then

For looper = 1 To count_rows

Selection.EntireRow.Insert

Next

End If

end_sub_Insert_Rows:

' error brings you here

End Sub

'===========================================================

' A HANDY MACRO FOR TAX SEASON AND SPREADSHEETS FULL OF DATA

‘ All rights reserved --------------------------------------

' To use this, highlight non contiguous fields and run the code below:

'--------------------------------------

'

' MACRO TO add non-contiguous cells (why didn't Microsoft build this in?)

'

Sub Collect_and_add_cells()

Dim Total As Currency

Dim cell As Range ' what a cell really is

Dim r As Range

Set r = Selection

' SET is important -- it is a handle to an object

' it will reappear in months to come

 

For Each cell In r

 

' MsgBox ("Address of " & cell.Address)

' MsgBox (" ... adding " & cell.Value & " to Total Amount ... " & Total)

 

Total = Total + cell.Value

 

Next ' for each cell

 

MsgBox ("The total of these " & _

numberOfCells & " cells is " & Total)

End Sub

'==================================================

 Record Macros using Relative position

(You will probably not get these right the first time.)

____________________________________________________

Sub MOVE_AFTER_SUMMING()

' the SUM (SIGMA button) moved the active cell down by one

'

ActiveCell.Offset(-1, 0).Range("A1").Select

Selection.Cut

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveSheet.Paste

ActiveCell.Offset(1, -1).Range("A1").Select

End Sub

 

Sub RE_SUM_MS_MONEY_TOTALS()

ActiveCell.Rows("1:2").EntireRow.Select

Selection.Delete Shift:=xlUp

ActiveCell.Offset(0, 6).Range("A1").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-1]C)"

Selection.Cut Destination:=ActiveCell.Offset(0, 1).Range("A1")

ActiveCell.Offset(0, 1).Range("A1").Select

End Sub