Select repeating range?

[SHRIMPLab]

Greetings.

This is likely a simple problem but I haven't found a solution yet (at least not one I understand) so I hope to harness the considerable power of your collective knowledge.

I have a worksheet with data arranged in one column (B). Each block of data (of which there can be a varying number) is 148 lines long and separated by the word 'Finished' plus a blank line. The ultimate goal is to select each block of data and transpose it as a new row in another workbook.

My problem is how to select each block of data without directly naming it? B1:B148, B151:B298,.... just won't do.

I hope I was clear enough. Obviously I have very little VBA experience so I'd appreciate any help you could offer. Thanks in advance.

-- Windows XP, Excel 2003. --

 

[hiker95]

SHRIMPLab,

Welcome to the MrExcel forum.


Because of the size of your data sets, this may be the best way to get help.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[texasalynn]

take a look at this post as it is similar

 

[SHRIMPLab]

Thanks for the replies and the welcome.

texaslynn, I had a look at the similar post and while I understand the concept (to find a start and end point and select everything in between) I'm not sure how to modify it for my situation. Basically I'd like to translate this into VBA-ese:

"Select 148 lines in column B, skip two lines, select the next 148 lines, skip two, and so on until you reach the end. Pretty please."

Here's a link to a sample data file for those that are interested: http://www.box.net/shared/cjx9pmilo3

The 'Data' sheet is what is spit out at me and the 'Result' sheet is the beginning of what I'd like to achieve.

Thanks again.

 

[hiker95]

SHRIMPLab,

Thanks for the workbook.


Sample data before the macro:

Excel Workbook
	A	B
1	SHRIMP Analytical Conditions for	Monday Feb 28 2011 10:23:28
2
3	IP	588
4	Session	11-032
5	Kohler	100b
6	Spot	6266-8.1
7	Source Slit (m)	80
8	Alpha Y inner (mm)	24
9	Alpha Z outer (mm)	22.75
10	Alpha Z (mm)	24
11	Energy Slit inner (mm)	0
12	Energy Slit outer (mm)	24.5
13	Collector Slit (m)	90
14	PBM range	10nA
15	PBM value (nA)	-5.3
16	SBM range =	300pA
17	SBM value (pA)	67.1
18	Primary Energy
19	bits	43597
20	V	10000.3
21	Source Steering Y Deflection
22	bits	-2833
23	V	20.6
24	Source Steering Z Deflection
25	bits	-854
26	V	5
27	Matching Lens
28	bits	16484
29	V	3777.4
30	Matching Lens Steering Y Deflection
31	bits	-2738
32	V	20
33	Matching Lens Steering Z Deflection
34	bits	1413
35	V	12.6
36	Transfer Lens
37	bits	15453
38	V	3539.3
39	Wien Magnet
40	bits	-8566
Data

After the macro (not all 148 columns shown for brevity):

Excel Workbook
	A	B	C	D	E	F	G
1	SHRIMP Analytical Conditions for		IP	Session	Kohler	Spot	Source Slit (m)
2	Monday Feb 28 2011 10:23:28		588	11-032	100b	6266-8.1	80
3	Monday Feb 28 2011 10:46:13		588	11-032	100b	6266-8.2	80
4	Monday Feb 28 2011 11:00:57		588	11-032	100b	1242-1.1	80
5	Monday Feb 28 2011 11:23:43		588	11-032	100b	6266-13.1	80
6	Monday Feb 28 2011 11:39:59		588	11-032	100b	1242-2.1	80
7	Monday Feb 28 2011 11:57:23		588	11-032	100b	1242-3.1	80
8	Monday Feb 28 2011 12:16:18		588	11-032	100b	6266-10.1	80
9	Monday Feb 28 2011 12:31:00		588	11-032	100b	10356-2.1	80
10	Monday Feb 28 2011 12:57:40		588	11-032	100b	10356-11.1	80
11	Monday Feb 28 2011 13:10:16		588	11-032	100b	10356-5.1	80
12	Monday Feb 28 2011 13:22:53		588	11-032	100b	10356-6.1	80
13	Monday Feb 28 2011 13:36:02		588	11-032	100b	10356-11.1	80
14	Monday Feb 28 2011 13:54:37		588	11-032	100b	10356-10.1	80
15	Monday Feb 28 2011 14:09:24		588	11-032	100b	6266-12.1	80
16	Monday Feb 28 2011 14:24:39		588	11-032	100b	10356-12	80
17	Monday Feb 28 2011 14:42:23		588	11-032	100b	10356-16.1	80
18	Monday Feb 28 2011 14:56:29		588	11-032	100b	10356-22.1	80
19	Monday Feb 28 2011 17:23:29		588	11-032	100b	6266-11.1	80
20	Monday Feb 28 2011 17:36:09		588	11-032	100b	1242-4.1	80
21	Monday Feb 28 2011 17:48:49		588	11-032	100b	10356-13.1	80
22	Monday Feb 28 2011 18:01:27		588	11-032	100b	10356-15.1	80
23	Monday Feb 28 2011 18:14:06		588	11-032	100b	10356-17.1	80
24	Monday Feb 28 2011 18:26:42		588	11-032	100b	10356-21.1	80
25	Monday Feb 28 2011 18:39:19		588	11-032	100b	10356-25.1	80
26	Monday Feb 28 2011 18:51:57		588	11-032	100b	10356-27.1	80
27	Monday Feb 28 2011 19:04:34		588	11-032	100b	10356-28.1	80
28	Monday Feb 28 2011 19:17:13		588	11-032	100b	6266-11.2	80
29	Monday Feb 28 2011 19:29:53		588	11-032	100b	1242-5.1	80
30	Monday Feb 28 2011 19:42:34		588	11-032	100b	10356-29.1	80
31	Monday Feb 28 2011 19:55:11		588	11-032	100b	10356-31.1	80
32	Monday Feb 28 2011 20:07:48		588	11-032	100b	10356-32.1	80
33	Monday Feb 28 2011 20:20:24		588	11-032	100b	10356-33.1	80
34	Monday Feb 28 2011 20:33:00		588	11-032	100b	10356-37.1	80
35	Monday Feb 28 2011 20:45:37		588	11-032	100b	10356-38.1	80
36	Monday Feb 28 2011 20:58:15		588	11-032	100b	10356-40.1	80
37	Monday Feb 28 2011 21:10:54		588	11-032	100b	6266-12.2	80
38	Monday Feb 28 2011 21:23:33		588	11-032	100b	1242-6.1	80
39
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SHRIMPLab()
' hiker95, 03/03/2011
' http://www.mrexcel.com/forum/showthread.php?t=533361
Dim w1 As Worksheet, wR As Worksheet
Dim c As Range, firstaddress As String
Dim NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Data")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
wR.Range("A1").Resize(, 148).Value = Application.Transpose(w1.Range("A1:A148").Value)
NR = 1
With w1.Columns(1)
  Set c = .Find("IP", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      NR = NR + 1
      wR.Range("A" & NR).Resize(, 148).Value = Application.Transpose(w1.Range("B" & c.Row - 2 & ":B" & c.Row + 146))
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
Application.ScreenUpdating = True
End Sub

Then run the SHRIMPLab macro.

 

[SHRIMPLab]

Excellent, hiker95. Excellent. It works perfectly.

Many thank yous for taking the time to write the entire macro for me.

Now to show it off and take all the credit...