Collect non contiguous data (excel 2002 /2003)

[braddy60]

Hi

In the attached spreadsheet in columns B,C,I,J,K,L,M data us pulled in from other sheets and goes into a seperate range, but this creates non contiguous rows, ie there is data in rows 30,31 and 245,26. Is there a way I can tranfer this data to Columns O to Z but this be in contiguous rows ie rows 30 to 34

Many Thanks

Braddy

[sdckapr]

I am not sure what you are after exactly.

If you are creating code, you can select the region (noncontguous) and loop thru the range (each area and each cell in that area) putting the items into the next cell.

Also if you are coding, you could also, copy each contiguous section separately Copy 30/31 then 245/246

If you need more details could you be more specific about what you need...

The cells aslo are linked and am not sure exactly how that enters into it since we don't have access to the source data.

Steve

[sdckapr]

If I understand, a scheme with formulas could be:

In A30 enter: 1
In A31 enter:
<pre>=A30+IF(B31=0,0,1)</pre>

Copy A31 to A32:A439

Delete A52:A64
In A65 enter:
<pre>=A51+IF(B65=0,0,1)</pre>


Delete A243:A244
In A245 enter:
<pre>=A242+IF(B245=0,0,1)</pre>


These Column A values may be hidden by format- cells-number(tab) -custom (without the quotes) ";;;"

In n29:N244 fill in the series from 1 to 216. These Column N values may be hidden by format- cells-number(tab) -custom (without the quotes) ";;;"

In O26 enter2, in P26 enter 3, in V26:Z26 enter 9-13 These row 26 values may be hidden by format- cells-number(tab) -custom (without the quotes) ";;;"

In O29 enter the formula"
<pre>=IF($N29>MAX($A$30:$A$439),"",VLOOKUP($N29,$A $30:$M$439,O$26,0))</pre>

Copy it to P29
Copy it to V29:Z29
Copy B30:M30 and paste-special formatting to O29:Z29

Copy o29:Z29 to O30:Z244

The scheme works by incrementiing the values in col A if there is something in column B. Then you dod a VLOOKUP with the values in N on these values using the row 26 as the column

Steve

[braddy60]

Hi Steve

You understand correctly what I am trying to do

I tried to apply the instructions you gave it looks to work OK except I cant get the fourth Item to transfer, Incidently I would like to apply this right down to row 555,

I have attached the file to show how far I have got, as you will see the fourth Item does not transfer over.

Thanks Braddy

[sdckapr]

I don't think you followed the directions exactly...
You have (eg) in A246
<pre>=A2453+IF(B246=0,0,1)</pre>


It should be:
<pre>=A245+IF(B246=0,0,1)</pre>


I think from A246 to the end, you have copied the wrong formula. Copy A246 (after changing it) down the column. It can go to A555 if you want. As I detailed specifically above, you will have to delete the cells between the "regions" (A440:A441, A513:A514, A535:A536) and change the first in each region (A441, A514, A536) to refer to the last in the previous region instead of the cell above (you can just edit <F2> in the cell and drag the blue outline from the cell above to the last cell in the region above and it will change the formula for each one.)

You will then have to change the lookup range from 439 to 455. Selecting the output region and doing and Edit - replace (Find: $436 Replace with: $455) should do it without too much trouble.

To get rid of the NAs you need to fill a number in column N or change the intial IF to also put a null if = zero...

Steve

[LegareColeman]

Does this code do what you want? If so, I would suggest calling it from the worksheet activate event routine so that this portion of the worksheet is updated every time the sheet is activated.

<pre>Public Sub Consolidate()
Dim I As Long, J As Long
J = 0
With Worksheets("Sheet1")
.Range("O29:Z244").ClearContents
For I = 28 To .Range("B65536").End(xlUp).Row - 1
If .Range("B1").Offset(I, 0).Borders(xlEdgeTop).LineStyle <> xlLineStyleNone And _
.Range("B1").Offset(I, 0).Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
If .Range("B1").Offset(I, 0).Value <> 0 Then
.Range("O29").Offset(J, 0).Value = .Range("B1").Offset(I, 0).Value
.Range("P29").Offset(J, 0).Value = .Range("C1").Offset(I, 0).Value
.Range("V29").Offset(J, 0).Value = .Range("I1").Offset(I, 0).Value
.Range("W29").Offset(J, 0).Value = .Range("J1").Offset(I, 0).Value
.Range("X29").Offset(J, 0).Value = .Range("K1").Offset(I, 0).Value
.Range("Y29").Offset(J, 0).Value = .Range("L1").Offset(I, 0).Value
.Range("Z29").Offset(J, 0).Value = .Range("M1").Offset(I, 0).Value
J = J + 1
End If
End If
Next I
End With
End Sub
</pre>

[sdckapr]

<img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Nice compact code.

One comment:
Your code gets a runtime error (type mismatch) on the line:
<pre>If .Range("B1").Offset(I, 0).Value <> 0 Then</pre>


Since B29 is "NSI" so can not be compared to zero. SInce Braddy was not interested in transfering this row (based on his initial question) you could change the line from:
<pre>For I = 28 To .Range("B65536").End(xlUp).Row - 1</pre>


to
<pre>For I = 2<font color=red>9</font color=red> To .Range("B65536").End(xlUp).Row - 1</pre>


and "solve the problem", though if there is a possibility of other "text" in column B the logic will have to be changed to be able to account for text and numbers...

Steve

[LegareColeman]

Interesting, it does not get an error on XL2K. I think Steve has XL97, has anyone tried it on any other versions?

[HansV]

Your code runs without error on Excel 2002 SP-3.

[LegareColeman]

Must be a 97 problem. Thanks.

[braddy60]

Hi Legare

Sorry for the delay in replying.

I used your code in Excel 2003 and it worked fine with no errors, thanks for that, I also used Steve's version once I had corrected my errors and that worked fine.
What I liked about Steve's version with comprehensive instructions was I can adapt that for use elsewhere, because of my limited knowledge of VBA I can only use yours in this instance.

Having said that I am most grateful for both contributions thanks to all

Braddy

[sdckapr]

Yes. I use XL97.

IN XL97 VB seems very sensitive of the datatype. In cases like this I would have to check the type before comparing...

Since it is not a problem for the user or the newer versions, there is no need to worry about it.

Steve