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