PDA

View Full Version : VBA code to select rows between values



llokol
12-21-2008, 01:41 AM
I'm trying to sort a whole list of data that's been separated into blocks using a blank row and a row containing the value "a". I want to sort each block of data using VBA code because I'll need to add in extra code when this has worked.

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

---------

I can't get this code to work and I've tried so many variations that I'm going insane. Anyone help?

GTO
12-21-2008, 03:29 AM
Well, for goodness' sake's, don't go insane. Take out any private/sensitive/company info, and show us an example workbook. While your description and example code is a good start, for the simple-minded of us (such as yours truly), a provided quickie example workbook eliminates having to redo the work you've already done.

More importantly, I see that you just joined and this is your first thread. Congratulations, as you have joined a great site and will 'meet' folks who surpass any reasonable expectations in being helpful.

In regards to that, it is of course both the weekend and but a few days from Christmas, so your answer may be delayed a bit.

Again, welcome :-)

Mark

rbrhodes
12-23-2008, 02:11 AM
hi llokol,

Insane am us!!

Here's a first stab at it. It's a different approach than what you were trying. I could explain where you went wrong and I could even fix your code but I figure at this point you probably just want something that works.

Read the comments, play with it, let me know...