Ask a question »

Excel - Vba inserting row and keep range membership

July 2015


Excel - Vba inserting row and keep range membership




Issue


In VBA, how do I copy/insert (in place) a row and ensure that if the source row intersected a column range, that the new row would also be in the range and the range will grow by 1 (i.e. the inserted row).

If the cursor is on a row that is at the top of a range, the new row does not become a member of the range and the range doesn't increase size by one row.

xlUp and xlDown don't make a difference, nor does leaving them out (which Excel says is the best thing to do if you have intersecting columnar ranges).

Solution


I am making it completely automatic. You need not name the range. the macro "test" does it. only input you have to put it is you have to type the number of the row to be deleted for e.g. 2 or 3 or 4 when input box comes up. Macro "undo" undoes what the macro does.

The database is like this from A1 to A5

1
2
3
4
5

don't do anything just run the macro "test" (both macro should be copied in the module). Sorry this has become a convoluted macro. I tried to use "resize". somehow or other I did not succeed. Perhaps some expert can give a better solution. But this solution works . if you want the the range of named range is different modify this statement in the macro "test" to suit you

Range("A2:a4").Name = "myrange"

The macros are:

Macro 1
Sub test()
Dim r As Range, j As Integer, k As Integer, m As Integer
undo
Range("A2:a4").Name = "myrange"
Set r = Range("myrange")
m = WorksheetFunction.Count(r)
'MsgBox m
k = InputBox("type the number of the row to be selected")
Rows(k).Select
Set r = Range("myrange")
j = Range("myrange").Cells(1, 1).Row
'MsgBox j
Selection.Rows.Insert
If Selection.Row = j Then
ActiveWorkbook.Names("myrange").Delete
Range(Cells(Selection.Row, "A"), r.Cells(m, 1)).Name = "myrange"
End If
MsgBox Range("myrange").Address
End Sub



Macro 2
Sub undo()
Dim r As Range, c As Range
Set r = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
For Each c In r
If c = "" Then c.EntireRow.Delete
Next c
End Sub

Note


Thanks to venkat1926 for this tip
For unlimited offline reading, you can download this article for free in PDF format:
Excel-vba-inserting-row-and-keep-range-membership.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Excel - Vba inserting row and keep range membership » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.