offset variable number of rows

Question

I have a piece of code where a variable needs to determine how many rows to select, before I then insert copied cells the number of rows of the variable. the recorded version of what i'm talking about is:

    ActiveCell.Offset(1, 0).Rows("1:6").EntireRow.Select

I need the 6 to be a variable that I have a value in.  What is the formatting for this?

I then also have a line:

ActiveCell.Offset(0, 9).Range("A1:F1").Select

where I need the end of the range (in the sample, "F1") to be a variable (the same variable as the one used in the first statement above.

Thx.

Answer 1

You should be able to adapt the code I shared to your specific needs.

Say your loop index is I.

Then, declare a variable N and get its value from range("A" & I).value

Now, the range of interest to you is range("J" & I").resize(,N).

You can do whatever you want with the data in that range, Insert it elsewhere, transpose it, etc.  To get the specific code, use the macro recorder.

Do note that if you are looping from some starting row and going down, you will run into some "interesting" problems because you are inserting additional rows as you process each row.  I have no idea how Excel / VBA will react to such behavior.

So, if you are indeed going "down" the rows in the sheet, you should reverse the process and go up starting with the bottom-most row you mean to process.

what I actually need is for cells defined by row x (variable in each loop), column J, through cell defined by the value in row x, column A, to be transposed starting in row x, column J.

 

as an examle, if the loop is working on row 25, and A25 has 5 in it (because it is defined as count(j25:J50), and this particular line has 5 cells filled from J25-N25).  The loop i'm running will currently insert 4 copies of row 25 below row 25. Now I have J25-N25 replicated 4 times below it.  What I need is a line that takes J25-N25 (the length of the array being "A25.value"), and transpose that into J25-J29. 

 

to your point, if I can avoid selecting, even better for my code's efficiency.

 

Let me know of how i can do the above.

 

thx.

 

Answer 2

what I actually need is for cells defined by row x (variable in each loop), column J, through cell defined by the value in row x, column A, to be transposed starting in row x, column J.

as an examle, if the loop is working on row 25, and A25 has 5 in it (because it is defined as count(j25:J50), and this particular line has 5 cells filled from J25-N25).  The loop i'm running will currently insert 4 copies of row 25 below row 25. Now I have J25-N25 replicated 4 times below it.  What I need is a line that takes J25-N25 (the length of the array being "A25.value"), and transpose that into J25-J29. 

to your point, if I can avoid selecting, even better for my code's efficiency.

Let me know of how i can do the above.

thx.

Answer 3

If your variable is named N, use

ActiveCell.Offset(1, 0).Rows("1:" & N).EntireRow.Select

or better yet

ActiveCell.Offset(1, 0).EntireRow.resize(N).Select

The same concept applies to the 2nd select.  Use

...Range("A1").resize(6,N)

Of course, you almost never have to select something to work with it.  What are you doing after you make the respective selections?

As an aside, if the new and improved forum UI has a way to format code for readability, I don't know what it is. :(