Add new Rows with VBA.

[DocAElstein]

Hello,
I’m adding new / empty Rows (in this case 8463 Rows, starting at Row 21).
The first code I got using the Macro recorder. (It simply adds rows one after the other). I modified it to reduce the steps by putting a simple loop in. It works but takes ages (even if I turn the screen off with Application.screenupdating = False ).

Sub Insert8463RowsAtRow21ByInserting8463Rows()
    Rows("21:21").Select
    For i = 1 To 8463 Step 1
    Selection.Insert Shift:=xlDown
    Next i
End Sub 'Insert8463RowsAtRow21ByInserting8463Rows()

The Second Code I got as well from the recorder. (It shifts everything down to make a space equal in Rows to the number of Rows I want.) That works quite quickly so I’m Happy. (I took the line Range("A8483").Activate out because I couldn’t see that it did anything. I hope that was OK??)

Sub Insert8463RowsAtRow21ByMovingEverything8463Downwards()
    Rows("21:8483").Select
    Selection.Copy
    Rows("8484:8484").Select
    ActiveSheet.Paste
    Rows("21:8483").Select
    '      Range("A8483").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub 'Insert8463RowsAtRow21ByMovingEverything8463Downwards()

But I am learning VBA and wanted to do it more professionally. I’ve tried a couple of hours to find a simple line that looks something like.

“ Rows(“21:21”) . Add .AddRows insert Range( 21 21 : 8483 8483 ).Add Rows.Insert( ) etc. etc. “

But I haven’t found it yet. It’s probably obvious to a Profi. – can anyone help
Thanks, Danke
Alan
Germany.

 

[DocAElstein]

An Andrew Poulson.
. Thanks, I think I almost understand. It is good to try to understand exactly wot is going on. But it still confuses me that books talk about “working down the hierarchy with the .Dot Principle.” Clearly here it is just a complicated confusing way of having a combination of commands: The Rows property Rows(21) is a thing or an object . Or rather it becomes one or “You get it” (“it is returned”) when you write Rows(21). You make it bigger with Resize(8463). For some strange reason you call this step a property (and not a command or Function which seems more sensible!?.) .. The last step Insert seems sensible as a simple method. But I still struggle to see why you did not call Resize a method. (Or why you do not say that the Insert has “returned “ an object, that is to say the “Range” object Range 21 : 8484
. Very confusing to a beginner, but I appreciate your help.
Alan

P.s you say “ there are no objects, but two properties and a method. “ then you go on to say “ The Rows property returns a Range object “ and also “ …..Resize property is applied to that object and returns a (larger) Range object “. Then you could almost say that there are 2 objects and a method.!?! Very confusing again. (to me)!!!

 

[Andrew Poulsom]

You need to forget about "working down the hierarchy with the .Dot Principle". Objects are returned by properties (and sometimes by methods). The objects returned have their own properties/methods, which can be applied to them using the dot. So what the line of code is doing is applying the Insert method to the Range object returned by the Resize property when applied to the Range object returned by the Rows method. There is no "hierarchy".

Sometimes what's called a property could also be called a method (as with Resize). In fact the Cells property used to be called the Cells method, if I recall correctly.

 

[DocAElstein]

Hi, Sorry to be a pedantic pain.. again.

How about this, that is to say have I got it right?

.1) There is no “OOP hierarchy with the .Dot Principle” (wot I originally tried to understand in my first Threads http://www.mrexcel.com/forum/genera...nce%85-o00o-%60-_-%60-o00o-oop-hierarchy.html and http://www.mrexcel.com/forum/questi...durch-workbooks-oder-windows-oop-methode.html
) Contrarily to what I have heard in learn Videos and read in books.?



.2) The code

Sub RowsaddWigi()
   Rows(21).Resize(8463).Insert
End Sub [COLOR=#008000]'RowsaddWig[/COLOR]i()

Is or can be seen to be three Methods (or Procedures)!!? Or one Object and 2 methods (or procedures)??. This last variation looks the most sensible to the naked (inexperienced!) eye. ( This was my very first suggestion ; “Object.Procedure.Procedure “)?? BUT please tell me if I am wrong. I am getting very confused and am very keen to learn and get it right
Thanks again.
Alan

P.s.
Sorry yet another last attempt to understand

It’s just a list of commands
010 apply a method to make an object, or rather get hold of one that already exists the Range object “Row 21”

020 apply a method to make it bigger, that is to say add extra bits to the range. –Resize(by adding 8463 rows)

030 Put this bigger Range in at the point where the original range was. –Insert

P.s.2 can you help with my other Question here the one to “Wigi”…. For example how to find a list (with explanations if possible) to the argument options to, for example the Insert Method(procedure)

 

[Andrew Poulsom]

There is an object hierarchy which can be traversed using the dot qualifier. For example:

Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value = 123

But it's important to grasp that that code uses properties to return objects. Workbooks returns a workbook object, Worksheets returns a worksheet object and Range returns a Range object. All three of those properties are read-only. It's coincidence that their names happen to be similar to the names of the objects that they return. Note that there isn't a Rows, Resize or Cells object. The Value property is read-write so it can return a value or be changed.

To see a list of the arguments for a property or method and what it returns (if anything) search for it in the Object Browser (F2 in the Visual Basic Editor). The definition will be shown in the window at the bottom. To get a detailed Help topic press F1.

 

[DocAElstein]

Hi !

I don’t quite see how you get all the info from the Object browser. Rows and Cells do say something about being a property as Range. So that sort of ties up. And makes sense: It’s a sensible choice of to have a range as a object perhaps as a row might be different size in different Excel versions. And I think correctly written one should write for example for accessing the first cell Cells(1,1).Range(“A1”), so just writing Cells(1,1), assumes the smallest Range “A1” ( it could of course be any Range, Depending on what is written in the () of Range() ) Similarly by writing Range(“A1”) , Excel assumes you have written Cells(1,1).Range(“A1”). )(OR there is an additional Range object that is that which starts at 1, 1 ) (see Thread http://www.mrexcel.com/forum/excel-...g-copy-paste-multiple-ranges.html#post3843992 )

I’m not quite sure yet about Worksheets and Workbooks: In the Object Browser Worbooks is said as being of the Class Workbooks. And Worksheets is said as being as a Class of sheets.

I see no way yet in the object browser to get at info about the (optional) arguments such as those arguments Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove from the method Insert. ( ……… .Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove )

If that sort of info is in the all encompassing Excel F1, then I haven’t found that yet either. One occasionally finds the argument and explanations thereof for a limited number of the Methods in books. But I’d still be interested in finding a full list.

I agree that it’s important to grasp that “ code uses properties to return objects. “ I certainly had not read that anywhere so I’m very grateful that you informed on that one! Thanks. ( I have seen written that a method or procedure can return an object (as well causing a result such as changing a property, etc. etc. ). But the idea of a property returning an object? – Weird to say the least. I suppose I could that say by having a nice flat lawn instead of messy uneven mud, I have created a garden. But really the method of digging and planting has given the resulting property which then gives me a garden. It’s certainly a tricky one, but as you mentioned, some properties were perhaps previously methods. (Maybe it’s something to do with the new “just in time “ computing idea” – It’s made as and only when you walk on it. ).
Alan

 

[Andrew Poulsom]

In this statement:

Cells(1, 1).Range("A1")

Cells(1, 1) returns a Range object - the cell in the first row and first column on the active sheet, ie A1. Then Range("A1") is applied to that object. That returns Range("A1") relative to Range("A1"), which of course is Range("A1"). Run this code and examine the result:

MsgBox Cells(1, 1).Range("B2").Range("B2").Address

Does it return what you expect?

If you search for Workbooks in the Object Browser, there is a Workbooks Class that is a member of Excel in the first row, and a Workbooks Property that is a member of Excel.Application in the second row. The same applies to Worksheets. If you press F1 when the line is selected you will get Help on the selected item. If I press F1 on the Range's Insert method (Function in the description window) I get something like this:

Range.Insert Method (Excel)

That site is a good source of Help.

 

[DocAElstein]

>>>>>>>>>>“ ……………Run this code and examine the result:

MsgBox Cells(1, 1).Range("B2").Range("B2").Address

Does it return what you expect? “ :-

I get, that is to say it returns, exactly what I expect and that confirms the discussion I had over exactly this in http://www.mrexcel.com/forum/excel-...g-copy-paste-multiple-ranges.html#post3843992 . – There appears to be some confusion over whether this result is correct. Assuming the “relative referencing “ idea, then the result is correct, as I expected. So you have cleared this point up. Thanks.
……………………..
>>>>>>>>>> .Cells( y , x ) (with no following .Range(“A1”)
I now have it clear that the Cells( y , x ) “Property” returns the range which is that one cell , co-ordinates y , x in a Worksheet. So it is not a case as I was thinking that Excel assumes you mean Cells ( y , x ).Range(“A1”) . I am not yet clear on what is going on when the Range “property?” is applied directly without a range object such as in “ Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value = 123 “
Is : - 1) this assuming that Cells( 1 , 1 ) is written before….
OR
; - 2) As you previously suggest, there is an unfortunate usage of the same name for different things. And we are dealing with the Range Object here, and the Range object or a worksheet is not really a relative range, but an exact Range (that is to say that range in a worksheet shown as.. A B C along the top and 1 2 3 along the side) ?

(In either case this could still explain how I cured some strange inconsistent bugs that I had with Range. – The problems seemed to disappear when I occasionally selected the first cell 1 , 1 )
…………………………………..
>>>>>>>>>>>>>>>>>>>>>
“ If you search for Workbooks in the Object Browser, there is a Workbooks Class that is a member of Excel in the first row, and a Workbooks Property that is a member of Excel.Application in the second row. The same applies to Worksheets. If you press F1 when the line is selected you will get Help on the selected item “ :-
. The nearest I can get to what I want , or what you suggest, is the following:-

. >> F2 >> select the Excel Library in the box upper left in the Object catalog Window. >> >> in the left Column (In German Titled Klassen) I select Range >> The right column now changes to (in German) ‘Elemente von Range’ >> I select Insert >> in the box at the bottom I have now ‘Insert([Shift],[CopyOrigin])’.
. That’s the best I get. Regardless of what I have selected F1 gives me an empty Office Help Window with a box to type in something to find. It doesn’t find Insert or Range. I am using Excel 2007 and 2010. Possibly the extra F1 Help function specific to what you select in the VBA Window is only available from 2013?

But I have your link now Range.Insert Method (Excel) which I did not manage to find over Google. (The information is overwhelming and split up all over the place. That is why I still strive to find a full concise list of everything). So that link is a help, Thanks (Although the explanations for the arguments are still not too clear).

>>>>>>>>>>>>>>>>>>>>
The idea that a “property returns an Object.”
I slept on this one. I still find this a Weird Idea. I cannot paint with Blue paint to get Blue House. (I tried- it just make a mess everywhere). I can apply the Method or procedure of painting with the (optional argument :- color) to get a house’s property change to being a different color. ?!?!?! That seems to work.
P.s. In German the word for Properties is Eigenschaft. I manage to ask some VBA teachers on this one. They all found the idea of an Eigenschaft returning an object weird. The nearest idea any one of us came to that was the use of Eigenisse (Events) to do things idea… (Worksheet_SelectionChange() etc-.)

 

[Andrew Poulsom]

The Range property is a member of multiple objects, including Range and Worksheet.

The definition of the Range object in VBA help states that it "represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range".

I don't know what's happened to your VBA Help system, although I do know that it has become less helpful with every version of Excel. Don't you have a Search box at the top with binoculars to the right of it?

 

[DocAElstein]

I don't know what's happened to your VBA Help system, although I do know that it has become less helpful with every version of Excel. Don't you have a Search box at the top with binoculars to the right of it?

Hi
Yes I do have a Search box at the top with binoculars to the right of it, which I am looking at now in Excel 2007 and 2010. If I type in, for example, Insert, then a box just under (which had been until then empty), gives me now many Rows and 3 columns with Column Headings: Library ; class ; Element. All of the rows have the word Insert somewhere either alone or as part of another word.
If I select the row with Excel ; Range ; Insert , then again I get in the box at the bottom ‘Insert([Shift],[CopyOrigin])’. But there is no other infomation, and by highlighting this ( or anything else) and clicking F1 I get as before an empty Office Help Window with a box to type in something to find. It doesn’t find Insert or Range when I type that word in the search box in this Office Help Window.!
Basically the search with the Search box (at the top with binoculars to the right of it) has just done what I originally did manually

 

[DocAElstein]

The Range property is a member of multiple objects, including Range and Worksheet.

The definition of the Range object in VBA help states that it "represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range".

So in “Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value = 123 “ are we using the Range object or the Range Property of Worksheets("Sheet1") ... If it is the Range Property then it might explain why I need to initially select Cells(1 , 1) to avoid strange inconsistant errors sometimes.