Select by format

[andrew.mitchell]

I'm trying to write a macro that can automatically select a range of
cells (across rows & columns) based on the format (bold) of the cells
above it and below it (also bold) so they can be sorted by date (which
is in one of the columns in the range). Now while the range of columns
will remain steady the number of rows will vary (and some in the
worksheet will not nedd to be sorted at all (having singular entries).
I know I can use the Offset property to select the rows below and
above, but can't figure a way of selecting the range of cells between
the two bolded cells. Does anyone know how to do this?

 

[Stopher]

I'm trying to write a macro that can automatically select a range of
cells (across rows & columns) based on the format (bold) of the cells
above it and below it (also bold) so they can be sorted by date (which
is in one of the columns in the range). Now while the range of columns
will remain steady the number of rows will vary (and some in the
worksheet will not nedd to be sorted at all (having singular entries).
I know I can use the Offset property to select the rows below and
above, but can't figure a way of selecting the range of cells between
the two bolded cells. Does anyone know how to do this?

So you have something like

<bold>ABC</bold>
AEF
AEG
<bold>ADF</bold>

hope the html worked

So you would select the range ABC,AEF,AEG and ADF??

Stopher

 

[Stitch2007]

Hi Stopher,
No, I am trying to select
AEF
AEG
as I want to sort these but not the bolded lines.
Cheers.

 

[Stopher]

Start by selecting the search row, find the first cell with cell
property bold. Hold the cell reference as a variable. Search down the
colum till the next cell with bold property nad hold that as a
variable. Then the select will be first variable +1 and the second cell
will be second variable -1.

Sound like a plan, or did you want some code.

I dunno seeing your a government worker, working for CRS (j/k)

Stopher

 

[Stitch2007]

Some code would be great, as I'm still scratching around with code a
bit. But if you can't see past the obviously envious position I have
at an esteemed agency such as CRS then I might be able to work it out.

 

[Stopher]

Sub test()

Range("A2").Select
rng = Range(Selection, Selection.End(xlDown)).Count
For i = 2 To rng

If Cells(i, 1).Font.Bold = True Then
startselect = Cells(i, 1).Row + 1
Exit For
End If
Next
Range("A" & i).Select

i = i + 1

For i = i To rng

If Cells(i, 1).Font.Bold = True Then
endselect = Cells(i, 1).Row - 1
Exit For
End If
Next
Range("A" & startselect, "A" & endselect).Select

End Sub

This what you want?? If you want to go again for the next gap between
then you will have to incorperate this in another for next. Where this
will probably fails is if there is no bold line at the end. But this is
a start for what you want I guess, now that the range is selected you
can d what you want with it......

Stopher