Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row and fill down formulae
Hi all,
I normally don't dabble in VBA / Macros as I feel out of my depth. However I would like to set up a "hopefully simple" macro and need your assistance. I have a formula in column R and column S. What I would like to do is create a macro to: 1) Insert a row from the current position - Inserting a row will never be at the first or the last row; always somewhere in the middle 2) Copy (or fill down) the formulae from columns R & S in the row above. I hope this makes sense. PS - I have found threads that could theoretically help, except for the fact that I have breaks in rows between different sets of data - for example, I have 10 rows of data, a break of a few rows, then another 10 rows of data. The break of a few rows are blank and should hopefully remain so. Many thanks for your help, Simon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row and fill down formulae
This assumes the user will select a cell somewhere on the sheet for the row
to be inserted. To assign this to a button, first create the button from the Control Tool Box, rightclick the button, then click view code from the pop up menu. Paste the code below into the code window. Private Sub Commandbutton1_Click() Rows(ActiveCell.Row).Insert Range("R" & ActiveCell.Row - 1 & _ ":S" & ActiveCell.Row - 1).Copy Range("R" & ActiveCell.Row).PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False End Sub "Simon" wrote in message ... Hi all, I normally don't dabble in VBA / Macros as I feel out of my depth. However I would like to set up a "hopefully simple" macro and need your assistance. I have a formula in column R and column S. What I would like to do is create a macro to: 1) Insert a row from the current position - Inserting a row will never be at the first or the last row; always somewhere in the middle 2) Copy (or fill down) the formulae from columns R & S in the row above. I hope this makes sense. PS - I have found threads that could theoretically help, except for the fact that I have breaks in rows between different sets of data - for example, I have 10 rows of data, a break of a few rows, then another 10 rows of data. The break of a few rows are blank and should hopefully remain so. Many thanks for your help, Simon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row and fill down formulae
Many thanks, this is exactly what I needed. Most kind.
"JLGWhiz" wrote: This assumes the user will select a cell somewhere on the sheet for the row to be inserted. To assign this to a button, first create the button from the Control Tool Box, rightclick the button, then click view code from the pop up menu. Paste the code below into the code window. Private Sub Commandbutton1_Click() Rows(ActiveCell.Row).Insert Range("R" & ActiveCell.Row - 1 & _ ":S" & ActiveCell.Row - 1).Copy Range("R" & ActiveCell.Row).PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False End Sub "Simon" wrote in message ... Hi all, I normally don't dabble in VBA / Macros as I feel out of my depth. However I would like to set up a "hopefully simple" macro and need your assistance. I have a formula in column R and column S. What I would like to do is create a macro to: 1) Insert a row from the current position - Inserting a row will never be at the first or the last row; always somewhere in the middle 2) Copy (or fill down) the formulae from columns R & S in the row above. I hope this makes sense. PS - I have found threads that could theoretically help, except for the fact that I have breaks in rows between different sets of data - for example, I have 10 rows of data, a break of a few rows, then another 10 rows of data. The break of a few rows are blank and should hopefully remain so. Many thanks for your help, Simon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto fill formulae when inserting rows | Excel Discussion (Misc queries) | |||
Insert row with formulae without contents | Excel Programming | |||
Changing font or fill-color when using formulae | Excel Discussion (Misc queries) | |||
Macro to Insert a row and copy Formulae | Excel Programming | |||
macro to insert pair of formulae in x number of rows -URGENT!!! | Excel Programming |