#Macro for inserting rows in excel mac how to#
#Macro for inserting rows in excel mac update#

On Error Resume Next 'to handle no constants in range - John McKee 1 Resize(rowsize:=vRows).Insert Shift:=xlDown Selection.Resize(rowsize:=2).Rows(2).EntireRow. Brown, programming, Grouped sheetsĭim sht As Worksheet, shts() As String, i As Long 'then delete '.EntireRow' in the following line 'if you just want to add cells and not entire rows 'How many rows do you want to add?', Title:='Add Rows', _ĭefault:=1, Type:=1) 'Default for 1 row, type 1 is number David McRitchieĪ 'So you do not have to preselect entire row ' row selection based on active cell - rev. Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) '- this macro shows on Tools, Macro., Macros (Alt+F8) dialog These are then inserted below the selected row. If I add the macro to the command button, the code that I currently have to do the insert, looks at the active cell, offsets this by moving one cell above it, and then asks how many rows to insert. Therefore, what I was looking at doing was ensuring that when the user wanted to insert a row, it was always automatically selected where this row would be added. My idea was to remove the need for the user to select a row initially. I am aware that a macro can be ran from the button, but to do this I will need to ensure that the user selects the correct row to insert the new row into. Regarding adding a command to run the macro from. As there will be multiple ranges that need to be expanded, dependant on where I add a row to, I will need to modify this to suit, so will investigate this. I have added the code to expand the range, and this appears to do what I would like it to.

#Macro for inserting rows in excel mac update#

Is it possible to update the named range to include the new row? If I insert a new row at the bottom of this category, this row is not included in the named range. Currently, M6:M10 has a named range defined, which is then refered to in M11. Is there any kind of OnClick of Cell event?Ģ.

Obviously, if a new row is added, the text will move down, so I can't just reference the current cell. the row above where the 'Insert New Row' text appears). This would ensure that the user always adds the new row to the end of the category (i.e. I would like to ensure that the code runs when this text is clicked. In the example below, you will see in cell C11 the text 'Insert New Row'. I would like to attach the code to a particular cell, so that it always runs from that cell. I am using code to insert a new row and copy formula (code courtesy of ).

#Macro for inserting rows in excel mac how to#

There are two things that I can not fathom out how to perform, and wondered if anyone could point me in the right direction.ġ.

I'm more of an Access user than Excel but have been asked to produce a worksheet that does certain tasks and have become a little stuck with some of the functionality.