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?
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?