Making ranges in a macro dynamic?

[sadia1991]

Hello!

This is the macro I have recorded:

Range("A1:G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A1:G212").Select
    Selection.Copy
    Windows("arrangement.xlsx").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("L1:L55").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("MINING.xlsx").Activate
    Range("RA73:RA127").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("!2014-05-15 experiment").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A1").Select
End Sub

How do I make the ranges in red (above) dynamic?
for the line Range("A1:G212").Select I was trying to select a block of cells (which I can do by pressing ctrl + A and then shift+down arrow OR ctrl+shift+down 12 times then shift+up arrow once)

for the line Range("RA73:RA127").Select I was trying to select a block of cells adjacent to the cell already active in the sheet (the idea is that I would paste a block of cells then repeat the macro and the next block of cells would get pasted in the next column etc.)


Thank you for taking the time to read and respond to my question! Any help is greatly appreciated!

 

[mrmmickle1]

You should define the Last Row with data like this:

[COLOR=#0000ff]Dim[/COLOR] LastRow [COLOR=#0000ff]As Long[/COLOR]

LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

and then use this variable in your range references:

[COLOR=#000000]Range("A1:G" & LastRow).Select [/COLOR]

To offset the selection over one column to the right use this:

Range("RA73:RA127").Offset(, 1).Select

Hopefully this will help. Let me know if you have anymore questions.

 

[sadia1991]

Thank you mrmmickle1 for your reply! but I'm really confused, I'm not sure what to look for in the lastrow, I was hoping maybe if I put a sample data here my question would make more sense:

A B C D E F G (column in excel)

Start	Date:	06/21/12
End	Date:	05/15/14
Subject:	F1O
Experiment:	0
Group:	0
Box:	3
Start	Time:	11:29:20
End	Time:	11:58:25
MSN:
C:	1299
A:
	0:00	1	30	9000	750	100
	5:00	0.5	3000	3	500	500
	10:00	0
B:
	0:00	45	0	0	15	30
	5:00	0	13	0	2	369
	10:00	9	855	0	0	1
	15:00	0.08	3001	0	-1	0
	20:00	30	228	6	15	198
	25:00:00	200	0	0	0	0
	30:00:00	0	96	3	0
D:
	0:00	211.82	6	15	30	45
	5:00	362	0	220.15	184	188
	10:00	0
E:
	0:00	0.17	0.29	0.22	0.12	0.22
	5:00	0.04
F:
	0:00	32.26	0.34	0.03	0.04	0.04
	5:00	0.1	0.02	0.07	0.08	0.1
	10:00	0.26	0.77	0.53	0.27	0.03
G:
	0:00	0.88	777	3.93	777	1.51
	5:00	777	2.23	777	0.87	777
	10:00	1.98	777	1.06	777	1.51
	15:00	777	1.39	777	1.01	777
	20:00	1.71	777	1.03	777	0.89
	25:00:00	777	1.71	777	1.86	777
H:
	0:00	0.09	0.13	777	0.49	1.25
	5:00	1.37	1.75	2.31	777	0.3
	10:00	777	0.74	1.18	777	0.15
	15:00	777	0.72	0.91	777	0.24
	20:00	777	0.46	777	0.54	777
	25:00:00	0.1	0.2	777	0.62	0.71
	30:00:00	0.93	777	0.11	0.16	777
	35:00:00	0.07	777	0.11	0.48	0.58
	40:00:00	777	0.09	0.58	0.81	777
I:
	0:00	30.06	0.44	0.47	0.13	0.3
	5:00	0.19	0.05	0.72	0.11	0.13
	10:00	0.33	0.8	0.6	0.37	0.15
	15:00	2.97	0.44	0.11	0.32	0.39
	20:00	0.28	0.19	0.92	777	30.14
	25:00:00	0.84	0.06	0.39	0.07	0.22
	30:00:00	0.26	0.05	0.1	0.42	0.53
	35:00:00	0.13	0.12	0.52	0.13	1.71
	40:00:00	0.48	0.26	0.13	0.87	0.13
	45:00:00	0.51	0.13	0.1	0.99	0.07
	50:00:00	0.47	0.21	0.04	777	30.83
	55:00:00	0.67	0.06	0.41	0.37	0.31
J:
	0:00	0.1	0.04	0.02	0.52	0.06
	5:00	0.06	0.02	0.4	1.01	0.42
	10:00	0.17	0.55	0.12	0.04	1001
	15:00	39.75	0.22	0.16	0.1	0.05
	20:00	0.32	0.03	0.09	0.09	0.31
	25:00:00	0.03	0.44	0.3	0.03	0.04
	30:00:00	0.55	0.1	1001	32.78	0.38
	35:00:00	0.06	0.12	0.86	0.13	0.57
	40:00:00	0.02	0.04	0.5	0.03	0.23
	45:00:00	0.03	1001	0.72	0.04	0.07
	50:00:00	0.37	0.02	0.22	0.03	0.64
	55:00:00	0.98	0.02	0.1	0.03	1001
	60:00:00	38.35	0.05	1.28	0.02	0.46
	65:00:00	1.17	0.04	1001	33.42	0.23
	70:00:00	0.28	1.03	0.72	1.05	1001
	75:00:00	0.58	0.17	0.42	0.6	0.02
	80:00:00	0.02	0.5	0.11	0.02	0.21
	85:00:00	0.26	1001	33.17	0.02	0.7
	90:00:00	0.04	0.25	0.14	0.09	0.73
K:
	0:00	40.92	0.45	0.03	0.36	0.05
	5:00	0.08	0.04	0.03	0.08	0.24
	10:00	0.23	0.08	0.08	0.32	0.09
	15:00	1001	35.76	0.29	0.03	0.16
	20:00	0.35	0.24	0.55	1001	37.81
	25:00:00	0.02	0.06	1001	40.01	0.15
	30:00:00	0.72	0.02	0.02	0.19	0.04
	35:00:00	1001	32	0.49	0.06	1.62
	40:00:00	0.62	0.03	0.02	1001	0.08
	45:00:00	0.85	0.02	1001	33.05	0.1
	50:00:00	0.43	0.04	0.04	0.03	0.03
	55:00:00	0.05	0.04	0.1	0.02	0.04
	60:00:00	0.11	0.03	0.02	0.03	1001
	65:00:00	33.25	0.3	0.04	0.05	0.04
	70:00:00	0.03	0.11	0.58	0.04	0.14
	75:00:00	0.25	0.47	0.04	1001	0.03
	80:00:00	0.18	0.02	0.03	0.54	0.04
	85:00:00	0.11	0.03	0.24	0.03	0.23
	90:00:00	0.74	0.24	0.28	1001	0.73
	95:00:00	0.1	0.16	0.02	0.6	0.1
	100:00:00	0.14	0.05	0.07	0.03	0.18
	105:00:00	0.1	0.03	0.26	0.19	0.2
O:
	0:00	1	-1
P:
	0:00	1	-1	-1	1	-1
	5:00	1	1	-1	-1	1
	10:00	-1	1	1	-1	1
	15:00	1	-1	1	-1	-1
	20:00	1	1	-1	-1	1
	25:00:00	-1	-1	1	1	-1
empty row
Start	Date:	06/15/12
End	Date:	05/25/14
Subject:	F2O

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

etc.... the data for the next subject is similar to above (same variables) BUT the variables E: F: G: H: I: J: K: O: and P: can have different number of rows. eg. one subject may have 22 data points in "G:" while another may have 135.

I want to be able to select the whole block for the subject and the empty row between that and the next block. then copy and paste this into another sheet (that extracts the important data and then copy Range("L1:L55") from this 2nd sheet into a 3rd sheet where I'm compiling the data. In the 3rd and final sheet I want to be able to paste the data in adjacent columns automatically (the offset doesn't work because I have to repeat this process many times- a few 1000- and I don't want to always paste it in the column next to "RA73:RA127")

I hope this makes sense! I really really appreciate your help. Thank you so so much for taking the time to read this.

 

[sadia1991]

ahhh so sorry about that my column headings got reformatted but basically all my data is in columns A-G

 

[mrmmickle1]

sadia1991,

It's difficult to decipher what is going on based on the sample data being formatted funny in Post # 3, but I think you want to do something like this. It will increment through columns one at a time up to Column 550... :

[COLOR=#0000ff]Sub [/COLOR]Test()

     [COLOR=#0000ff]  Dim[/COLOR] i [COLOR=#0000ff]As Integer[/COLOR]

    [COLOR=#0000ff]   For[/COLOR] i = 469 [COLOR=#0000ff]To[/COLOR] 550 [COLOR=#0000ff]Step[/COLOR] 1 [COLOR=#008000]'469 signifies Column RA the next selection will be Column RB (470)[/COLOR]

                Range(Cells(73, i), Cells(127, i)).Offset(, 1).Select

[COLOR=#008000]           'Your Paste Data Code Here[/COLOR]

     [COLOR=#0000ff]  Next[/COLOR] i

[COLOR=#0000ff]End Sub[/COLOR]

I am confused as to how your data is laid out. If you can get a better example of the data I can probably help you further.

Do you know you many iterations you need?

 

[sadia1991]

This is what the data looks like: (the different variables E: F: G: H: I: J: K: O: and P: can have different number of rows.)
https://drive.google.com/file/d/0B5d-7iW5ZlFwTjFlY1pfRUxYMEk/edit?usp=sharing

I want to be able to select the whole block for each 'subject' and the empty row right below it (everything in black border in the above file). then copy and paste this into another sheet (that extracts the important data and then copy Range"L1:L55" ( this files gives me a single column of 55 cells that I copy) from this 2nd sheet into a 3rd sheet where I'm compiling the data.

In the 3rd sheet I want to be able to paste the data in adjacent columns, sample sheet here:
https://drive.google.com/file/d/0B5d-7iW5ZlFwY3YtdF9OR2Vzd1U/edit?usp=sharing
so if I already had the first 2 columns of data i'd want the next column in C10:C64, and the next in D10:D64 etc...

I need to repeat this macro at least 5000 times. would that change the code in your previous reply?

Thank you so so much for helping me out!

 

[sadia1991]

update!!
I found a way to copy the data from the first file!! using

Range("$A$1").CurrentRegion.Select

now I need to figure out how to paste it in the correct position in the 3rd file!

so sorry for taking up so much of your time. really appreciate all your help!

 

[mrmmickle1]

sadia1991,

I got a chance to look at your files. I had a few questions.

So you want to copy and paste this data in blocks. It appears to me that one row above the Third occurrence of the word "Start" in column A could define a block. (Is This Correct?) If so this link may prove useful: Find Nth Occurance Also Is a block always the same number of rows? Say 100 rows? (The main importance is if the number is static....

So when you say paste this data into another sheet what is the sheet named? Is this a calculation sheet? Once the data is pasted to this calculation **** I assume it spits out numbers in column L1:L55? You want to copy this data into a brand new sheet until you run out of subjects to complete this process on?

How many subjects to you think you have? 5000? Anyway to get a solid number?

I think I may be able to help you with your task. I just need to drill down the specifics of the process so I can understand what you would like for an end result.....

 

[sadia1991]

Hi mrmmickle1!!
I was able to figure out the code, thank u so much for all your help. I ended up using the offset function and it did the job!

I do have another question tho; is there a way to make the selection in the offset function dynamic? Ie if in my macro I'm selecting all cells with a specific number in them, then I want to 'offset' the selection (select the cells 2 rows directly below the current selection. But the number of cells selected in the first step vary. How would the code change for this?

Thank you and have a lovely week!

 

[mrmmickle1]

sadia1991,

Take a look at the Resize property. It sounds like what you need: CLICK HERE FOR SOME INFO