excel macro

[kevinluebb]

I've got data that is roughly structured like this:


A B C D E
1 AAAA AAAAA AAAAA AAAAAA AAAA
A B C D E
1 aa aa aa aa aa
2 BBBB BBBBBB BBBBB BBBBBB BBBB
3 BBBB
4 CCC CCCCC CCCC CCCCC CCC
5 CCC CCCCC CCC
6 CCCC

I need to be able to sort this data but as you
can see I would lose the relationship for the
data in E3.
What I need to do is check A3 for data, if blank
slide over to E3 and see if it has data. If there
is something in E3, I would need to move it to F3.
I'm not familiar with Excel macros and worked in
Lotus macros many moons ago.

 

[SkipVought]

Hi,
You are not clear.

What about Columns B, C & D?
What is the significance of Col F?
Why do you have Columns and Row 1 repeated twice?
What are you really trying to do?

Inquiring minds need to know Skip,
metzgsk@voughtaircraft.com

 

[kevinluebb]

The data got messy when it was posted.

A B C D E F
1 aa aa aa aa aa
2 BBBB BBBBBB BBBBB BBBBBB BBBB
3 BBBB
4 CCC CCCCC CCCC CCCCC CCC
5 CCC CCCCC CCC
6 CCCC

line 2 has data in all columns, cell e3 has data that is related to the record in line 2. i need to be able to check say a3 for blanks, if it is blank, slide over to e3,take the data there and move it up to f2 so that it retains its relationship to the line 2 record. Otherwise when i sort it, the data currently in e3 will lose its relationship with the line 2 record when the sort is on column A.

 

[kevinluebb]

can I attach a portion of the original spreadsheet to an email? It would be easier if you can see what I'm doing.

 

[SkipVought]

Not true! The ENTIRE row sorts when you sort on ANY column!!!

IF....

you sort the ENTIRE TABLE and NOT JUST a COLUMN Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

How are you sorting this table? Tell me EXACTLY, step-by-step what you are doing. Where is your active cell, what is selected, what toolbuttons / menu items are you selecting Skip,
metzgsk@voughtaircraft.com

 

[kevinluebb]

the sort range would be a1 .. e6
sort key would be column a

 

[kevinluebb]

can i lop off a portion of the actual spreadsheet and shoot it to you? at least you can then see what I'm working with.

 

[SkipVought]

Listen,
I does NOT take a MACRO to sort a table. In your specific case, if I sort on A, here's what I get for the ENTIRE table ...

        A          B           C           D            E
1       AAAA       AAAAA       AAAAA       AAAAAA       AAAA
2       BBBB       BBBBBB      BBBBB       BBBBBB       BBBB
4       CCC        CCCCC       CCCC        CCCCC        CCC
5       CCC                    CCCCC                    CCC
3                                                       BBBB
6                                                       CCCC

Skip,
metzgsk@voughtaircraft.com

 

[kevinluebb]

I know that but look at the output.....the series of data represented by B ....the second to last line of data in E is no longer associated with the original series.

 

[SkipVought]

Don't have unzip on my pc here, sorry Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

1. I have Named you column ranges using Insert/Name/Create - selecting Top

2. Here is a spreadsheet function in column J...

=IF(ISBLANK(CONTROL_NUMBER),EXCEPTIONS,"")

Still do not know what this has to do with SORT. Skip,
metzgsk@voughtaircraft.com

 

[kevinluebb]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/1/02 by ISD
Range("a1&quot.Select
For i = 1 To 30
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Offset(0, 0).Value = "" Then
ActiveCell.Offset(-1, 9).Value = ActiveCell.Offset(-1, 9).Value + " " + ActiveCell.Offset(0, 8).Value
Selection.EntireRow.Select
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).Activate
End If
Next i
End Sub


Never mind....this works. i've only set it for 30 reps for the test but will add a routine to check for the last row.
NOW the data can be SORTED and the integrity of the original is retained.