Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select by format
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select by format
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select by format
Hi Stopher,
No, I am trying to select AEF AEG as I want to sort these but not the bolded lines. Cheers. Stopher wrote: wrote: 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 <boldABC</bold AEF AEG <boldADF</bold hope the html worked So you would select the range ABC,AEF,AEG and ADF?? Stopher |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select by format
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select by format
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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select by format
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format - Doing without using .Select? | Excel Discussion (Misc queries) | |||
Select rows to format | Excel Programming | |||
Select a Range and Format | Excel Programming | |||
format first row within select | Excel Programming | |||
When I select "format cells", the format dialog box does not disp. | Excel Worksheet Functions |