VBA Data Munging - deleting unwanted stuff

[ChrisJH]

First off - first post and I have been Thankful for Mr. Excel for years of finding answers to questions.

I'm working on a VBA that should be pretty easy once broken down into its parts. I'll continue to plug away at those parts for now but maybe one of you can whip this up between sips of coffee.

I have opened a TXT file. In it there are all kinds of text strings, numbers, and blank cells I don't want interspersed with data I want to keep. There is a repetitive nature to these data. Here's what I think, logically, the macro needs to do:

Everything is in Column A
Find: if(isnumber()) (e.g., the next number is in (A5))
Always, 4 cells below this, is the text title of the section (in this case, A9)
Concatenate(A5&". "&,A9) and put it into H1 (the start of a list)
Copy the following rows (until and not including the first BLANK) and put them, starting in H2

Now repeat - finding the next number.

I'm conceptualizing either the code deleting entire rows and creating the list after deleting what I don't want OR, conversely, taking the stuff I want and moving it to a new column.

Here's some of the junk I'm dealing with... (I've bolded what I want to keep)


* “Scenario-Based Training Intervention Notebook Marking Sheet” (2 copies for each recruit)
?


15
1
18
2
Learning Demonstration
* Introduction (3 minutes)
* Learning Demonstrations (45 minutes)
* Summary (2 minutes)

Objectives
* describe the Defensive Tactics test
* explain the evaluation protocols/expectations
* put on the protective suit
Provide each student with a laminated copy of the Rubric and Assessment Form that will help them to focus on things to look for in a scenario.

At the end of the assessment, advise the students that on the test date, the participant in the testing will be directed to a location away from peers who have not yet tested to prepare their notes on the intervention in “Notebook Entry”. If they have time, they can correct it using the “Scenario Based Training Intervention Notebook Marking Sheet”. Copies of both documents will be provided. The notes and their marking sheet are to be turned in to the .
* Laminated (available from the Lesson 18 binder)
?
?
n/a





*

* Laminated Assessment Form” (available from the Lesson 18 binder)
?







*

* “Notebook Entry” (2 copies for each recruit)
?







*

* “Scenario-Based Training Intervention Notebook Marking Sheet” (2 copies for each recruit)
?


16
2
19
6
Test #1
* Introduction (2 minutes)
* Testing Procedures (98 minutes)
* Re-testing Procedures

Objectives
* apply an intervention option based on the totality of the situation
* apply tactics and skills in a dynamic environment

Test I (Appendix 19.1) (Facilitator)
?
?

 

[Micron]

How many rows might there be? Looping over column A to test if each value is a number is one way to find the starting point but it can become slow and inefficient. I'm wondering about coding something like =MATCH(TRUE,INDEX(ISNUMBER(A1:A100),0),0) into B1 because to get those that many functions into code would be quite the kludge I think. Note: actually A100 would be a variable for the last row in column A that has data.

Based on what you posted:
- use B1 formula value as the starting row; copy/paste that.
- copy/paste values using Offset in a loop from 4 to 7
- use the row value of the last offset (row 12 in the first pass) and change B1 formula and replace A1 with 12. The result will be 47.
- add that to 12 (58) and start again from there
- rinse and repeat

I think it all boils down to the data repeating reliably. Thoughts?

 

[Micron]

I added another block to get a 3rd set of inputs. Code puts this out starting at H1.

15 Learning Demonstration
* Introduction (3 minutes)
* Learning Demonstrations (45 minutes)
* Summary (2 minutes)
16 Test #1
* Introduction (2 minutes)
* Testing Procedures (98 minutes)
* Re-testing Procedures
17 Learning Demonstration2
* Introduction (3 minutes)2
* Learning Demonstrations (45 minutes)2
* Summary (2 minutes)2