Excel data selection

[celestedeeva]

Hello all,

I have a really messy Excel file and was wondering how I would go about selecting data based on the following example (VBA code would be good):

A B C D E
value
1 n 100
2 n 200
3 n 200
4 n 400

value
5 n 100
6 n 200
7 n 200
8 n 400

value
9 n 100
10 n 200
11 n 200
12 n 400

value
13 n 100
14 n 200
15 n 200
16 n 400

Effectively I need to get so that I have the following formatted:
A B C
value
1 n 100
2 n 200
3 n 200
4 n 400
5 n 100
6 n 200
7 n 200
8 n 400
9 n 100
10 n 200
11 n 200
12 n 400
13 n 200
14 n 200
15 n 400
16 n 400

Is there any way that I can search the whole sheet for the word "value" and then retrieve all of the rows beneath "value" until it hits a blank, select the values column and the two colums to the left for each non blank row and then append to another worksheet.

Thanks for any advice -- I am stumped.

Celeste.

 

[GlennUK]

I'd put a pointer in column F as to which column the data starts for each row. It's then an easy task to extract the data from the entire block aligned, using something like the INDEX function. Any unwanted rows can be removed by several means, depending on taste.

To get you started, here is the formula ( an array formula, entered using Ctrl-Shift-Enter instead of Enter ), for column F:

=MATCH("value",OFFSET($A$1,MAX(IF($A$1:$E1="value",ROW($A$1:$E1),0))-1,0,1,5),0)-2

assuming a starting row of 1.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Hi,

Is this a one shot deal, or will this be an on-going problem?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Gavona]

Looks to me as if you could select column A.
Edit,GoTo,Special Blanks
Edit, Delete shift cells left

With recorder on you get this:

Sub Macro1()
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlToLeft
End Sub

Which simplifies to:

Sub Macro2()
    Columns("A:A").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
End Sub

hope that helps,

Gavin

 

[xlbo]

If you need VBA code, please post in the VBA forum: Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question