Seperateing Data

keithd · May 25th, 2005, 02:31 PM

Hello. This will be my last question

I have 4 Colums of Data, going down about 100 cells every 6 or so rows are specific information that can't be moved around. For instance
A B C D
1A|83|34|109
2A|32|34|65
3A|45|78|12
4A|12|89|45
5A|34|45|56
6B|56|90|56
7B|34|45|54
8B|23|78|12

This would go down A, B, C,D,E etc... where ever it stops

Since there is no Space between A and B (meaning i can't search for blank space to tell where specific information in Coloumn D begins or ends) How would I be able to Select the important information in Coloumn D belonging to A

If this is confuseing please let me know, and i'll try to reword it.

Thanks in advance for any help

Keith

BrianB · June 6th, 2005, 10:10 AM

Before proceeding. If you somehow want to make totals of the data a pivot table will do the job fast and accurately.

Here is some code to do what you asked (I think). Again, using Select is not a good way of doing things,

Code:

Sub test()
    Dim ws As Worksheet
    Dim FromRow As Long
    Dim StartRow As Long
    Dim EndRow As Long
    Dim MyVal As String
    Dim MyRange As Range
    '------------------------------------------
    Set ws = ActiveSheet
    FromRow = 1
    '------------------------------------------
    '- loop all data
    While ws.Cells(FromRow, 1).Value <> ""
        MyVal = ws.Cells(FromRow, 1).Value
        StartRow = FromRow
        '---------------------------------------
        '- loop column 1 value
        While ws.Cells(FromRow, 1).Value = MyVal
            FromRow = FromRow + 1
        Wend
        '---------------------------------------
        '- get column D cells
        EndRow = FromRow - 1
        With ws
            Set MyRange = _
                .Range(.Cells(StartRow, 4), .Cells(EndRow, 4))
        End With
        MyRange.Select
        '------------------------------------------
        'wait 1 second ( put what you want to do with the data)
        Application.Wait Now + TimeValue("00:00:01")
   Wend
End Sub

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.