Excel Rows selection with a variable

%3CLINGO-SUB%20id%3D%22lingo-sub-1768644%22%20slang%3D%22en-US%22%3EExcel%20Rows%20selection%20with%20a%20variable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768644%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20done%20some%20minimal%20experience%20of%20programming%20with%20VBA%20and%20am%20struggling%20with%20the%20correct%20syntax%20within%20excel..%3C%2FP%3E%3CP%3EI%20have%20a%20block%20of%20data%20that%20is%2018%2C000%20rows%26nbsp%3B%20by%208%20columns%20and%20I%20want%20to%20run%20a%20macro%20to%20count%2048%20rows%2C%20insert%20a%20blank%20Row%2C%20copy%20and%20paste%20the%20preceding%20cell%20into%20that%20Blank%20Row%20at%20one%20location%20and%20Copy%20%26amp%3B%20Paste%20an%20%22%3DAVERAGE(xxx%3Axxx)%22%20formula%20into%20a%20different%20location.%20Finally%2C%20I%20want%20to%20select%20the%2048%20row%20block%20of%20data%20and%20%22Group%22%20it%20so%20I%20show%20only%20the%20newly%20introduced%20row%20containing%20the%26nbsp%3B%20Average%20formula%20and%20the%20copy%20and%20pasted%20information.%3C%2FP%3E%3CP%3EI%20am%20not%20at%20all%20familiar%20with%20Macro's%20but%20I%20did%20did%20run%20through%20the%20sequence%20above%26nbsp%3B%20manually%20and%20record%20it.%20This%20is%20what%20I%20got%20%3A-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Macro6()%3CBR%20%2F%3E'%20Macro6%20Macro%3CBR%20%2F%3ERows(%22491%3A491%22).Select%3CBR%20%2F%3ESelection.Insert%20Shift%3A%3DxlDown%2C%20CopyOrigin%3A%3DxlFormatFromLeftOrAbove%3CBR%20%2F%3ERange(%22A490%22).Select%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22A491%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3ERange(%22I442%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ERange(%22I491%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3ERows(%22443%3A490%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Rows.Group%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20want%20to%20increment%20the%20selected%20locations%20in%20blocks%20of%2048%20and%20repeat%20the%20macro%20to%20the%20end%20of%20the%2018%2C000%20data%20items.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20only%20seem%20to%20find%20any%20examples%20of%20Rows.select%20or%20Range%2Cselect%20as%20having%20fixed%26nbsp%3B%20(string)%20expressions%2C%20but%20i%20need%20to%20use%20variables%20if%20i%20am%20to%20add%2048%20to%20it%20and%20loop%20to%20the%20next%20iteration%20of%20the%20macro.%3C%2FP%3E%3CP%3EI%20tried%20the%20following%20(with%20a%20variation%20of%20different%20delimiters%2C)%20but%20Excel%20does%20not%20seem%20to%20like%20any%20of%20them%20Giving%20%22%20compile%20error%2C%20expected%20list%20separator%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20Startrows%20As%20Integer%3CBR%20%2F%3EDim%20Numrows%20As%20Integer%3CBR%20%2F%3EStartrows%20%3D%20247%3CBR%20%2F%3ENumrows%20%3D%2048%3CBR%20%2F%3Eendrows%20%3D%20Startrows%20%2B%20Numrows%3CBR%20%2F%3EDebug.Print%20Startrows%3C%2FP%3E%3CP%3ERows(Startrows%3Aendrows).Select%3C%2FP%3E%3CP%3ESelection.Rows.Group%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20anyone%20help%20please%3F%3C%2FP%3E%3CP%3Ethanks%20Ray%20C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1768644%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New Contributor

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

0 Replies