I have done some minimal experience of programming with VBA and am struggling with the correct syntax within excel..
I have a block of data that is 18,000 rows by 8 columns and I want to run a macro to count 48 rows, insert a blank Row, copy and paste the preceding cell into that Blank Row at one location and Copy & Paste an "=AVERAGE(xxx:xxx)" formula into a different location. Finally, I want to select the 48 row block of data and "Group" it so I show only the newly introduced row containing the Average formula and the copy and pasted information.
I am not at all familiar with Macro's but I did did run through the sequence above manually and record it. This is what I got :-
Sub Macro6()
' Macro6 Macro
Rows("491:491").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A490").Select
Selection.Copy
Range("A491").Select
ActiveSheet.Paste
Range("I442").Select
Application.CutCopyMode = False
Selection.Copy
Range("I491").Select
ActiveSheet.Paste
Rows("443:490").Select
Application.CutCopyMode = False
Selection.Rows.Group
End Sub
The problem is that want to increment the selected locations in blocks of 48 and repeat the macro to the end of the 18,000 data items.
I can only seem to find any examples of Rows.select or Range,select as having fixed (string) expressions, but i need to use variables if i am to add 48 to it and loop to the next iteration of the macro.
I tried the following (with a variation of different delimiters,) but Excel does not seem to like any of them Giving " compile error, expected list separator".
Dim Startrows As Integer
Dim Numrows As Integer
Startrows = 247
Numrows = 48
endrows = Startrows + Numrows
Debug.Print Startrows
Rows(Startrows:endrows).Select
Selection.Rows.Group
Could anyone help please?
thanks Ray C
