Excel 2010 VBA macro

AxeTracks
AxeTracks used Ask the Experts™
on
I have a spreadsheet with 12 columns, the last column is a date.  I want to scan down this column and when the date changes, i want to insert an empty row.  The empty row will be used to sum the columns for that day.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
What do you mean by "when date changes"? Each day you want to insert an empty row? Why don't you use another column where you list the days on a column and on another column you put the sums for that day? You can do that easily with a SUMPRODUCT() formula. I've attached an example of what I'm saying.

jppinto

PS: If you still want a macro solution, it would help if you post a sample file and make a better description on what you want to do...
Sumproduct-Example.xlsx
Software Quality Lead Engineer
Top Expert 2011
Commented:
For the requested macro solution try the following which is keyed to the active sheet.

Chris
Sub addrow()
Dim thisrow As Long

    For thisrow = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row To 2 Step -1
        With ActiveSheet.Cells(thisrow, 12)
            If .Value <> .Offset(-1, 0) Then
                .EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
            End If
        End With
    Next
End Sub

Open in new window

hi

try this

Sub InsertMania()

'Insert row above active cell
ActiveCell.EntireRow.Insert

'Insert row below active cell
ActiveCell.Offset(1).EntireRow.Insert

'Insert column to the left of the active cell
ActiveCell.EntireColumn.Insert

'Insert column to the right of the active cell
ActiveCell.EntireColumn.Offset(0, 1).Insert

End Sub
by
kumaresan

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial