Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to insert rows
Can someone please help me wrtie a macro that will insert 1 blank row between
the end of one month and the start of another? Example: I want a row between 1/31/200 and 2/1/2000. I have a large spreadsheet I would like to do this with. Also, if possible, I would also like a formula inserted in that blank row that calculates the average for the month in column 2. 1/27/2000 5.69 1/28/2000 5.81 1/31/2000 5.84 2/1/2000 5.84 2/2/2000 5.88 -- Allan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to insert rows
Try the below macro with your data in Col A and Col B starting from
Row1...Hope you dont have any blank rows in between... Sub MyMacro() Dim lngRow As Long, lngRRow As Long lngRow = 2: lngRRow = 1 Do While Range("A" & lngRow) < "" If Month(Range("A" & lngRow)) < Month(Range("A" & lngRow - 1)) Then Rows(lngRow).Insert Range("B" & lngRow).Formula = "=SUM(B" & lngRRow & ":B" & lngRow - 1 & ")" lngRRow = lngRow + 1: lngRow = lngRRow End If lngRow = lngRow + 1 Loop Range("B" & lngRow).Formula = "=SUM(B" & lngRRow & ":B" & lngRow - 1 & ")" End Sub If this post helps click Yes --------------- Jacob Skaria "Flipper" wrote: Can someone please help me wrtie a macro that will insert 1 blank row between the end of one month and the start of another? Example: I want a row between 1/31/200 and 2/1/2000. I have a large spreadsheet I would like to do this with. Also, if possible, I would also like a formula inserted in that blank row that calculates the average for the month in column 2. 1/27/2000 5.69 1/28/2000 5.81 1/31/2000 5.84 2/1/2000 5.84 2/2/2000 5.88 -- Allan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A Macro to Insert Rows | Excel Discussion (Misc queries) | |||
Insert rows with condition using macro | Excel Discussion (Misc queries) | |||
Insert rows macro. | Excel Discussion (Misc queries) | |||
Insert Rows Macro | Excel Discussion (Misc queries) | |||
asking again, macro to insert rows | Excel Worksheet Functions |