Vba inserting row and keep range membership
Solved/Closed
jbean
-
8 Apr 2010 à 18:49
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 15 Apr 2010 à 06:12
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 15 Apr 2010 à 06:12
Related:
- Vba inserting row and keep range membership
- Saints row 2 cheats - Guide
- Vba case like - Guide
- Excel online vba - Guide
- Vba timer - Guide
- Vba excel mac - Guide
2 responses
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
15 Apr 2010 à 06:12
15 Apr 2010 à 06:12
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
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
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
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
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
9 Apr 2010 à 00:42
9 Apr 2010 à 00:42
Your question is not clear to me I am guessing
suppose data is like this column A blank column B having data
1
2
3
0
4
5
6
now experiment with this macro . see whether you get any idea to get at a solution of your problem
in the macro j gives you last row before and after insertion of row.
suppose data is like this column A blank column B having data
1
2
3
0
4
5
6
now experiment with this macro . see whether you get any idea to get at a solution of your problem
in the macro j gives you last row before and after insertion of row.
Sub test()
Dim j As Integer
j = Cells(Rows.Count, "b").End(xlUp).Row
MsgBox j
Range("a4").EntireRow.Insert
j = Cells(Rows.Count, "b").End(xlUp).Row
MsgBox j
End Sub
Thanks Venkat. This isn't quite what I'm looking for.
I have data in column A, rows 1 through 5.
Cells A2, A3, A4 are a range called myrange.
When the active row is 3, and I do a VBA copy/insert, the new row is in the range myrange bringing the total rows in the range to 4.
When the active row is 2 (in other words, at the top of the range myrange), the VBA copy/insert inserts a new row, BUT, the new row is not in the range myrange (myrange remains 3 rows big). The xlup and xldown don't make a difference.
I want the new, inserted row to be included in any range that is intersected by activerow (where the cursor is when I do the copy/insert).
Thanks much.
I have data in column A, rows 1 through 5.
Cells A2, A3, A4 are a range called myrange.
When the active row is 3, and I do a VBA copy/insert, the new row is in the range myrange bringing the total rows in the range to 4.
When the active row is 2 (in other words, at the top of the range myrange), the VBA copy/insert inserts a new row, BUT, the new row is not in the range myrange (myrange remains 3 rows big). The xlup and xldown don't make a difference.
I want the new, inserted row to be included in any range that is intersected by activerow (where the cursor is when I do the copy/insert).
Thanks much.
