Inserting Rows with Macro and expanding Named Range

[Graham T]

Hi

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.

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

1. I am using code to insert a new row and copy formula (code courtesy of http://www.mvps.org/dmcritchie/excel/insrtrow.htm). I would like to attach the code to a particular cell, so that it always runs from that cell. In the example below, you will see in cell C11 the text "Insert New Row". I would like to ensure that the code runs when this text is clicked. This would ensure that the user always adds the new row to the end of the category (i.e. the row above where the "Insert New Row" text appears). Obviously, if a new row is added, the text will move down, so I can't just reference the current cell. Is there any kind of OnClick of Cell event?

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

Test_Ashleigh.xls
	C	D	E	F	G	H	I	J	K	L	M
3	Category
4									Quantity	PricePerPerson	Total
5	Food:
6	TestFoodItemNo.1								4	10.00	40.00
7	TestFoodItemNo.2								1	15.00	15.00
8	TestFoodItemNo.3
9	TestFoodItemNo.4
10	TestFoodItemNo.5								1	1.00	1.00
11	InsertNewRow	Food:SubTotal									56.00
12	Miscellaneous:
13	TestMiscellaneousItemNo.1								2	10.00	20.00
14	TestMiscellaneousItemNo.2
15	TestMiscellaneousItemNo.3
16	TestMiscellaneousItemNo.4
17	TestMiscellaneousItemNo.5
18	InsertNewRow	Miscellaneous:SubTotal									20.00
Sheet1

Thanks in advance for any advice or pointers.

Graham

 

[brian.wethington]

1. There is an event of selection change however, that will only trigger each time a cell changes, therefore if it is already active and you click it again it will not trigger again. I would create a Command button (using the control toolbox toolbar) and you can use that to attach a macro to among other options.

I am still looking at your second part to see what I would do in this case.

 

[brian.wethington]

Here is a fairly simple idea of expanding a named range 1 row. In this case the named range I expanded was Rng1

Sub Test1()

Set C = Range("Rng1").Cells(Range("Rng1").Count, 1).Offset(1, 0)
Range("Rng1", C).name = "Rng1"

End Sub

Hope this helps!

 

[Graham T]

Hi Brian

Thanks for the replies.

I have added the code to expand the range, and this appears to do what I would like it to. 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.

Regarding adding a command to run the macro from. 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.

My idea was to remove the need for the user to select a row initially. 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.

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. These are then inserted below the selected row.

The code I am using at the minute is:

Sub InsertRowsAndFillFormulas_caller()
  '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog
  Call InsertRowsAndFillFormulas
End Sub
 
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' Documented:  http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows --   1997/09/24 Mark Hill <markhill@charm.net.noSpam>
   ' row selection based on active cell -- rev. 2000-09-02 David McRitchie
   Dim x As Long
   ActiveCell.Offset(-1, 0).Select
   ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
   If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If

   'if you just want to add cells and not entire rows
   'then delete ".EntireRow" in the following line

   'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
   Dim sht As Worksheet, shts() As String, i As Long
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name

    x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup

    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
     Resize(rowsize:=vRows).Insert Shift:=xlDown

    Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillDefault

    On Error Resume Next    'to handle no constants in range -- John McKee 2000/02/01
    ' to remove the non-formulas -- 1998/03/11 Bill Manville
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select
   

    Set C = Range("Cat1Totals").Cells(Range("Cat1Totals").Count, 1).Offset(1, 0)
    Range("Cat1Totals", C).Name = "Cat1Totals"

   
End Sub

Regards, Graham

 

[brian.wethington]

I am not sure if this will fit your needs but from what it sounds like I would do something like this.

First, name the cells that you wish to make your add cells.. I used insert1 for 4 cells in my tests.

Second, the worksheet code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Insert1")) Is Nothing Then
    Call InsertRowsAndFillFormulas 
End If

End Sub

I am not sure how familiar you are with event code, so this is worksheet event code and will run each time you select a cell in the sheet that you put it in. To put it in the sheet you need it for, right click on the tab of the sheet you would like, and select view code.
If you select more than one cell at a time the code will terminate before running. If you did not select a cell in the range that you designated Insert1 it will not run the call.

Just so you know it is not necessary to select in your case. This would allow you insert a row:

ActiveCell.Offset(-1, 0).EntireRow.Insert

Hopefully you can build what you need out of what I have provided.

 

[Graham T]

Brian

Thanks once again for the tips, although I think I have worked out a different method.

I have used the following to find a particular cell:

Sub Cat1RangeSelect()  'Dave Paterson, misc,
    Dim myRng As Range
    Set myRng = Worksheets("Sheet1").Range("Cat1SubTotal")
    With myRng
        .Parent.Select
        .Cells(.Cells.Count).Select
    End With
End Sub

My command button is on the same row as this cell, therefore I then run the insert row command, and then using the code you gave earlier I rename the range.

It seems to work, although I have had to replicate the same code and modify this for the 4 different instances of the different buttons. I know that this is not an efficient way of doing it, but it seems to work.

I would be happy to send you an example file of this if you are interested.

Regards, Graham

 

[MorganO]

Graham,

It looks like you posted this question twice, and I replied to the other message not knowing you had already had other working the issue. I am posting this here for you use.

Using the spreadsheet you included in your post and the code you refered to for inserting a row, I've put together a method for doing what you asked for.

To make this work you will need to do the following:

1) Make sure the row cell that the person clicks on to insert a row has the following as its value Insert New Row - The code uses this to determine where to insert the row
2) You need to include the name of your named ranges in the worksheet on the same row as the "Insert New Row" cell. The name for that row must be in the 'K' column. For example, on your sheet you would put Cat1Totals in cell K11 and Cat2Totals in Cell K18. You may have to unmerge cells to do this.

Here is the code I used. It is placed in the same sheet as your values:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
  If Target.Value = "Insert New Row" Then
    Application.EnableEvents = False
    Target.Offset(-1, 0).Activate
    Call InsertRowsAndFillFormulas(1)
    'Now update the named range
    'get the range name
    rangename = Target.Offset(0, 8).Value
    'get the range address
    rangeaddress = Range(rangename).Address
    'read the final row in the address - looking for the $
    i = Len(rangeaddress)
    Do Until Mid(rangeaddress, i, 1) = "$"
    i = i - 1
    Loop
    RowValue = Right(rangeaddress, Len(rangeaddress) - i)
    AddressValue = Left(rangeaddress, Len(rangeaddress) - Len(RowValue))
    NewNamedRangeValue = AddressValue & RowValue + 1
    ActiveWorkbook.Names.Item(rangename).Delete
    ActiveWorkbook.Names.Add Name:=rangename, RefersTo:="=" & NewNamedRangeValue
    Application.EnableEvents = True
  End If
End If
End Sub

Let me know if you have any questions.

Owen

Edit - After relooking at this code I realize that I didn't take into account updating the named ranges BELOW the changed row. I don't have anymore time today to look into this, but will attempt to find a solution. Hope this at least points in the right direction.

Owen

 

[Graham T]

Owen

Thanks for the reply. I may have mistakenly posted the question twice, as it didn't appear to post first time, although I thought that I had deleted the duplicate.

I have tried my example with your code, and find that this also does exactly what I was looking for, and probably far more efficiently that how I got it to work.

I also found that the example did appear to update my named ranges BELOW the changed row, as far as I can tell!

Thanks once again, Graham