Select rows between values

[jjokol]

I can't for the life of me work out how to select rows between values. For example, row 3 column a contains the value "a". The next 6 rows contain different numbers that I want to sort into order, then row 10 column a contains the value "a" again.

How can i programme some vba to sort the numbers between the "a"s? I have a huge long spreadsheet that I want to analyze.

 

[SkipVought]

Hi,

Your problem seems to be a bad spreadsheet table design. Add a column to act as a grouping sequencer. The sort on that new column AND the "different numbers that I want to sort into order". No VBA required.

Assuming that your new column is Z...
[tt]
Z2: =if(a2="a",Z1+1,Z1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

I would create a helper column A with a formula in A2 such as
=if(Or(B2="A",B1="A"),A1&"1",A1)
Copy A, PasteSpecial to values

Sort on A and B
Delete A

Job done. No slow looping etc.

No real point to using code if it is one off but record doing the above if you want.

Gavin

 

[jjokol]

Thanks for your suggestions - they work, but I want to execute some code after the figures have been sorted. That's why I wanted a vba solution.

Basically, the code needs to:
1. Select rows between "a" and "a".
2. Sort the rows into ascending order according to values in column D
3. Then execute some more code on what's been sorted.

I should've taken longer with my original question.

 

[Gavona]

Merry Christmas!
The Or in my solution is potentially necessary if your "different numbers that I want to sort" are in the same column as the As.
I think Skip's use of numeric values in the helper column is much more sensible than my suggestion!

but I want to execute some code after the figures have been sorted

The solution described can be implemented in code and will help you to identify the rows to apply your further code to.

With a bit of adaptation you could make it so that all the rows that you want to apply this further code to have a particular characteristic in a helper column. Filter on this column and use the VisibleCells property to select all the rows for the further processing.

Avoid looping if you can - it is slow!



Gavin

 

[SkipVought]

"Then execute some more code on what's been sorted."

Like What? Subtotals maybe?

It woud be much more productive for YOU, if you were to fully disclose how this table will function for you. There are many ways to skin a cat: some more efficient than others. You seem to have some odd ways in your mind.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjokol]

This is the code I've tried - but it doesn't work! Columns A-E have values:

Column A is empty until the last row which has a value of X.
Column C is the one I'm using as my active column.
All other columns have values that I want to examine.

At the beginning of each of the blocks of rows that I want examine is a row containing no values.
At the end of each of the blocks of rows that I want examine is a row containing the value "a" in column C.


My code is:

Dim c As Object
Set c = ActiveCell
Do Until Not IsEmpty(c.Offset(0, -2))
' this should stop code executing when it reaches the X

With c

If c.Value = "" Then
Set c = c.Offset(1, 0)

Do
ActiveCell.Rows.Select
Set c = c.Offset(1, 0)
Loop Until c.Value = "a"

ElseIf c.Value = "a" Then

Selection.Sort Key1:=Range(c.Offset(0, 2)), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Set c = c.Offset(1, 0)
End If

End With
Loop

End Sub

 

[Gavona]

but it doesn't work!

You really should be more explicit. Does it fail to compile? Does it return an error? On what line? Does it loop through your cells?

It would also be useful to see a couple of blocks of sample data so we can replicate what you are trying to do.

Have you tried stepping through the code watching what is happening in the workbook? That should tell you which line is not doing as you expect.

Rather than Selection.Sort I would explicitly tell it what range to sort. If a single cell is selected then Excel will probably guess ok given what you have said about your data. If a range (or Row) is selected then Excel will sort just that row. Better to use something like the following so you are in control:
c.CurrentRegion.Sort.

Try this (without code at first).

Click on the cell containing the first A

Ctrl-A (Select current region (area bounded by blank rows and columns)), this is the area you want to sort?

Ctrl-Down
Ctrl-Down
Ctrl-A
etc



Gavin

 

[Gavona]

Dependent on your exact data layout your code could be something like this:

Sub Macro2()
Dim c As Range
Set c = Selection

startLoop:
With c.CurrentRegion
    .Sort .............
    'Other stuff to process this region
End With

Set c = c.End(xlDown)
Set c = c.End(xlDown)
If c.Row > 65534 Then Exit Sub
GoTo startLoop
End Sub

Gavin

 

[SkipVought]

"Thanks for your suggestions - they work, but I want to execute some code after the figures have been sorted. "

Then use this solution to sort and use VBA to do the remainder.

BTW, a technique that I often use to define ranges like this is...

1. find the first row of the range, in this case the row after no value in column c

2. find the last row of the range, in this case the row before no value in column c row greater than first row.

Sub test()
    Dim lRow1 As Long, lRow2 As Long
    
    With ActiveSheet
        lRow1 = UsedRange.Row
        Do
            lRow2 = .Cells(lRow1, "C").End(xlDown).Row
            Intersect(.UsedRange, _
                .Range(.Cells(lRow1, 1), _
                .Cells(lRow2, 1)).EntireRow, _
                .Range(.Cells(1, "B"), .Cells(1, "IV").EntireColumn)).Sort _
                Key1:=.Cells(lRow1, "E"), Order1:=xlAscending, _
                Header:=xlNo, _
                OrderCustom:=1, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom
            lRow1 = .Cells(lRow2, "C").End(xlDown).Row
            If UCase(.Cells(lRow2, "A").Value) = "X" Or .Cells(lRow1, "A") = .Cells.Rows.Count Then Exit Do
        Loop
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}