adding two lines and removing total Free Excel Macros (VBA) | Excel Programs

Copy Macro

'For online Excel courses click: https://www.excelsizeyeter.com/excelwithkenan.com/udemy_giris.php 
Sub InsertTwoRowsAndSum()
Dim oldVal
Dim rwTop As Long
Dim colmn As Integer
Dim offColmn As Integer
If IsEmpty(ActiveCell.Value) Then
MsgBox "You must select the top of the column " & _
Chr(13) & "you want to work on"
Exit Sub
End If
'store the active cell's value and row number
'oldVal is used to compare to the active cell's value as it changes
'rwTop is used to refer to the first cell in the sum function
oldVal = ActiveCell.Value
rwTop = ActiveCell.Row
'this is the column that will be summed
colmn = 4
'determine the offset from the active cell's column
offColmn = colmn ActiveCell.
Column
If offColmn = 0 Then
MsgBox "the column being checked for cell entry " & _
"changes can not be the same as the one being summed."
Exit Sub
End If
86
'select the next cell
ActiveCell.Offset(1, 0).Select
'loop until an empty cell is encountered
While Not IsEmpty(ActiveCell)
'if the value of the cell is not the same as oldVal, insert a row
'and sum formulas
If ActiveCell.Value <> oldVal Then
'add row
ActiveCell.EntireRow.Insert xlUp
'add sum formulas
ActiveCell.Offset(0, offColmn).Formula = "=sum(" & _
Range(Cells(rwTop, colmn), _
Cells(ActiveCell.Row 1,
colmn)). _
Address(False, False) & ")"
'go down a row and insert another line
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert xlUp
'store this row number for use in the sum formula and update oldVal
rwTop = ActiveCell.Row + 1
oldVal = ActiveCell.Offset(1, 0).Value
End If
'go to the next row and loop if not blank
ActiveCell.Offset(1, 0).Select
Wend
'handle the sum and row inserts needed for the last set of values
ActiveCell.EntireRow.Insert xlUp
ActiveCell.Offset(0, offColmn).Formula = "=sum(" & _
Range(Cells(rwTop, colmn), _
Cells(ActiveCell.Row 1,
colmn)).Address(False, False) _
& ")"
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert xlUp
End Sub

No	Macro Name
1	adding two lines and removing total
2	excel operators hk
3	Adding Rows and Total Formula When Cell Changes
4	adding menu
5	adding date to a1 when closing book
6	convert double and single lines into different colors and designs (one line is bold and the other line is normal)
7	Adding a password to commandbuton
8	Add menu to file edit bar delete
9	add toolbars to combo box and select from there
10	add sheet / pages as menus
11	add excel sheets as menus
12	Can the characters typed in the inputbox appear as "*****" (password or password characters)?
13	Delete all modules in an Excel file at the same time
14	Open a new worksheet with a name entered in textbox1
15	specifies in detail how many references the book contains (on new sheet / page)
16	Write numbers to a1: a5 and warns c1 if their total is 100
17	system report
18	list formulas on new sheet / page
19	specifies in detail how many formulas are in the book (on new sheet / page)
20	Assigning cells by adding 2 rows to 100 other print columns

Excel Free Macros (VBA)

Adding Two Lines And Removing Total

Online Excel Courses

Services

How can we help you?

Subscribe to our Youtube Channel?

Keywords