Insert Blank Line and Sort

------------liX6Lxsnq7Ovs6LAASpU5y
Content-Type: text/plain; charset=iso-8859-15; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

Within a worksheet I have an example of data below consisting of four  
columns.

I need to put a blank line in between the Racecourse (where they change)  
and after do a sort on Rating (high to low).

Is it possible?

I could do it with a macro, but am unable to fathom out how to deal with a  
differing number of horses at each racecourse.

Time	Racecourse	Horse	Rating
04:30:00	SANDOWN	Dare To Dance	0.261
04:30:00	SANDOWN	Garud	0.153
04:30:00	SANDOWN	West Brit	0.073
04:30:00	SANDOWN	Rockerfellow	0.073
04:30:00	SANDOWN	Hope Point	0.073
04:30:00	SANDOWN	Nuba	0.073
04:30:00	SANDOWN	Eclipseoftheheart	0.073
04:40:00	UTTOXETER	Red Current	0.081
04:40:00	UTTOXETER	Bull Market	0.074
04:40:00	UTTOXETER	Thetasteofparadise	0.069
04:40:00	UTTOXETER	Claude Carter	0.046
04:40:00	UTTOXETER	Hollies Favourite	0.046
04:40:00	UTTOXETER	A Stones Throw	0.02
04:50:00	BATH	What Katie Did	0.038
04:50:00	BATH	Too Many Questions	0.075
04:50:00	BATH	My Meteor	0.089
04:50:00	BATH	Miss Firefly	0.091
04:50:00	BATH	Katmai River	0.093
04:50:00	BATH	Flaxen Lake	0.086
04:50:00	BATH	Crimson Queen	0.08
04:50:00	BATH	Bold Argument	0.075
04:50:00	BATH	Avonlini	0.043
05:00:00	SANDOWN	Billion Dollar Kid	0.058
05:00:00	SANDOWN	Formal Demand	0.222
05:00:00	SANDOWN	Jungle Bay	0.101
05:00:00	SANDOWN	Junket	0.058
05:00:00	SANDOWN	Kingarrick	0.006
05:00:00	SANDOWN	Mary's Pet	0.063
05:00:00	SANDOWN	Miss Bootylishes	0.148
05:00:00	SANDOWN	Push Me	0.065
05:00:00	SANDOWN	Tuxedo	0.084
05:00:00	SANDOWN	Whitechapel	0.21
------------liX6Lxsnq7Ovs6LAASpU5y
Content-Type: multipart/related; boundary=----------liX6Lxsnq7Ovs6KYvnCgwg

------------liX6Lxsnq7Ovs6KYvnCgwg
Content-Type: text/html; charset=iso-8859-15
Content-ID: <op.1311261249063.7cb02d546d5c2ea0@192.168.1.139>
Content-Transfer-Encoding: Quoted-Printable

<!DOCTYPE html><html><head><style type=3D"text/css">body { font-family:'=
Times New Roman'; font-size:13px; background:Window}</style></head><body=
><div>Within a worksheet I have an example of data below consisting of f=
our columns.</div><div><br></div><div>I need to put a blank line in betw=
een the Racecourse (where they change) and after do a sort on Rating (hi=
gh to low).</div><div><br></div><div>Is it possible?</div><div><br></div=
><div>I could do it with a macro, but am unable to fathom out how to dea=
l with a differing number of horses at each racecourse.<br><br><br>

<table border=3D"0" cellpadding=3D"0" cellspacing=3D"0" width=3D"340" st=
yle=3D"border-collapse:
 collapse;width:255pt">

 =

 =

 =

 =

 <tbody><tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" width=3D"64" style=3D"height:15.0pt;width:48pt">Time=
</td>
  <td width=3D"77" style=3D"width:58pt">Racecourse</td>
  <td width=3D"135" style=3D"width:101pt">Horse</td>
  <td width=3D"64" style=3D"width:48pt">Rating</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:30:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Dare To Dance</td>
  <td class=3D"xl66">0.261</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:30:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Garud</td>
  <td class=3D"xl66">0.153</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:30:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">West Brit</td>
  <td class=3D"xl66">0.073</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:30:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Rockerfellow</td>
  <td class=3D"xl66">0.073</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:30:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Hope Point</td>
  <td class=3D"xl66">0.073</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:30:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Nuba</td>
  <td class=3D"xl66">0.073</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:30:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Eclipseoftheheart</td>
  <td class=3D"xl66">0.073</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:40:00</td>=

  <td class=3D"xl66">UTTOXETER</td>
  <td class=3D"xl66">Red Current</td>
  <td class=3D"xl66">0.081</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:40:00</td>=

  <td class=3D"xl66">UTTOXETER</td>
  <td class=3D"xl66">Bull Market</td>
  <td class=3D"xl66">0.074</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:40:00</td>=

  <td class=3D"xl66">UTTOXETER</td>
  <td class=3D"xl66">Thetasteofparadise</td>
  <td class=3D"xl66">0.069</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:40:00</td>=

  <td class=3D"xl66">UTTOXETER</td>
  <td class=3D"xl66">Claude Carter</td>
  <td class=3D"xl66">0.046</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:40:00</td>=

  <td class=3D"xl66">UTTOXETER</td>
  <td class=3D"xl66">Hollies Favourite</td>
  <td class=3D"xl66">0.046</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:40:00</td>=

  <td class=3D"xl66">UTTOXETER</td>
  <td class=3D"xl66">A Stones Throw</td>
  <td class=3D"xl66">0.02</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:50:00</td>=

  <td class=3D"xl66">BATH</td>
  <td class=3D"xl66">What Katie Did</td>
  <td class=3D"xl66">0.038</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:50:00</td>=

  <td class=3D"xl66">BATH</td>
  <td class=3D"xl66">Too Many Questions</td>
  <td class=3D"xl66">0.075</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:50:00</td>=

  <td class=3D"xl66">BATH</td>
  <td class=3D"xl66">My Meteor</td>
  <td class=3D"xl66">0.089</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:50:00</td>=

  <td class=3D"xl66">BATH</td>
  <td class=3D"xl66">Miss Firefly</td>
  <td class=3D"xl66">0.091</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:50:00</td>=

  <td class=3D"xl66">BATH</td>
  <td class=3D"xl66">Katmai River</td>
  <td class=3D"xl66">0.093</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:50:00</td>=

  <td class=3D"xl66">BATH</td>
  <td class=3D"xl66">Flaxen Lake</td>
  <td class=3D"xl66">0.086</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:50:00</td>=

  <td class=3D"xl66">BATH</td>
  <td class=3D"xl66">Crimson Queen</td>
  <td class=3D"xl66">0.08</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:50:00</td>=

  <td class=3D"xl66">BATH</td>
  <td class=3D"xl66">Bold Argument</td>
  <td class=3D"xl66">0.075</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">04:50:00</td>=

  <td class=3D"xl66">BATH</td>
  <td class=3D"xl66">Avonlini</td>
  <td class=3D"xl66">0.043</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Billion Dollar Kid</td>
  <td class=3D"xl66">0.058</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Formal Demand</td>
  <td class=3D"xl66">0.222</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Jungle Bay</td>
  <td class=3D"xl66">0.101</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Junket</td>
  <td class=3D"xl66">0.058</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Kingarrick</td>
  <td class=3D"xl66">0.006</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Mary's Pet</td>
  <td class=3D"xl66">0.063</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Miss Bootylishes</td>
  <td class=3D"xl66">0.148</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Push Me</td>
  <td class=3D"xl66">0.065</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Tuxedo</td>
  <td class=3D"xl66">0.084</td>
 </tr>
 <tr height=3D"20" style=3D"height:15.0pt">
  <td height=3D"20" class=3D"xl65" style=3D"height:15.0pt">05:00:00</td>=

  <td class=3D"xl66">SANDOWN</td>
  <td class=3D"xl66">Whitechapel</td>
  <td class=3D"xl66">0.21</td>
 </tr>

</tbody></table></div></body></html>
------------liX6Lxsnq7Ovs6KYvnCgwg--

------------liX6Lxsnq7Ovs6LAASpU5y--
0
Saxman
7/21/2011 3:14:09 PM
excel 39879 articles. 2 followers. Follow

4 Replies
766 Views

Similar Articles

[PageSpeed] 3

On Thu, 21 Jul 2011 16:14:09 +0100, Saxman  
<"john.h.williams2(removethis)"@gmail.com> wrote:

> Within a worksheet I have an example of data below consisting of four
> columns.
>
> I need to put a blank line in between the Racecourse (where they change)
> and after do a sort on Rating (high to low).
>
> Is it possible?
>
> I could do it with a macro, but am unable to fathom out how to deal with  
> a
> differing number of horses at each racecourse.
>
> Time	Racecourse	Horse	Rating
> 04:30:00	SANDOWN	Dare To Dance	0.261
> 04:30:00	SANDOWN	Garud	0.153
> 04:30:00	SANDOWN	West Brit	0.073
> 04:30:00	SANDOWN	Rockerfellow	0.073
> 04:30:00	SANDOWN	Hope Point	0.073
> 04:30:00	SANDOWN	Nuba	0.073
> 04:30:00	SANDOWN	Eclipseoftheheart	0.073
> 04:40:00	UTTOXETER	Red Current	0.081
> 04:40:00	UTTOXETER	Bull Market	0.074
> 04:40:00	UTTOXETER	Thetasteofparadise	0.069
> 04:40:00	UTTOXETER	Claude Carter	0.046
> 04:40:00	UTTOXETER	Hollies Favourite	0.046
> 04:40:00	UTTOXETER	A Stones Throw	0.02
> 04:50:00	BATH	What Katie Did	0.038
> 04:50:00	BATH	Too Many Questions	0.075
> 04:50:00	BATH	My Meteor	0.089
> 04:50:00	BATH	Miss Firefly	0.091
> 04:50:00	BATH	Katmai River	0.093
> 04:50:00	BATH	Flaxen Lake	0.086
> 04:50:00	BATH	Crimson Queen	0.08
> 04:50:00	BATH	Bold Argument	0.075
> 04:50:00	BATH	Avonlini	0.043
> 05:00:00	SANDOWN	Billion Dollar Kid	0.058
> 05:00:00	SANDOWN	Formal Demand	0.222
> 05:00:00	SANDOWN	Jungle Bay	0.101
> 05:00:00	SANDOWN	Junket	0.058
> 05:00:00	SANDOWN	Kingarrick	0.006
> 05:00:00	SANDOWN	Mary's Pet	0.063
> 05:00:00	SANDOWN	Miss Bootylishes	0.148
> 05:00:00	SANDOWN	Push Me	0.065
> 05:00:00	SANDOWN	Tuxedo	0.084
> 05:00:00	SANDOWN	Whitechapel	0.21


///////////////////////////////////////////////////////////////////

I found this bit of code from the OzGrid site which inserts a blank row  
where there is a change of data.

Sub InsertDividers()
     '
     ' on active sheet, move down thru column A
     ' If current cell Is Not the same As previous
     ' insert a row.
     ' stop when current testing cell In A:A Is empty
     '
     Dim lngRow As Long

     lngRow = 3
     Do While Cells(lngRow, 1) <> ""
         If Cells(lngRow, 1) <> Cells(lngRow - 1, 1) Then
             ' change In NAME value so insert row
             Rows(lngRow).Insert
             lngRow = lngRow + 1
         End If
         lngRow = lngRow + 1
     Loop

End Sub

All I need is to sort each group of data in column 4 high to low.
0
Saxman
7/22/2011 4:14:08 PM
hi,

Sub Macro1()
rws = Range("B65536").End(xlUp).Row
For i = Range("B65536").End(xlUp).Row To 2 Step -1
  If Range("B" & i - 1) <> Range("B" & i) Then
   Rows(i & ":" & rws).Sort Key1:=Range("D" & i), Order1:=xlDescending, Header:=xlNo
   Rows(i).Insert Shift:=xlDown
   rws = i - 1
  End If
Next
End Sub


-- 
isabelle

0
isabelle
7/22/2011 5:01:54 PM
On Fri, 22 Jul 2011 18:01:54 +0100, isabelle <i@v.org> wrote:

> hi,
>
> Sub Macro1()
> rws = Range("B65536").End(xlUp).Row
> For i = Range("B65536").End(xlUp).Row To 2 Step -1
>   If Range("B" & i - 1) <> Range("B" & i) Then
>    Rows(i & ":" & rws).Sort Key1:=Range("D" & i), Order1:=xlDescending,  
> Header:=xlNo
>    Rows(i).Insert Shift:=xlDown
>    rws = i - 1
>   End If
> Next
> End Sub
>

Thanks isabelle.  Had a quick go at this and it certainly sorts.  I just  
need to amend the ranges to 'D' I think?  Off to a beer festival now.   
Will have another go Saturday.
0
Saxman
7/22/2011 5:57:02 PM
On Fri, 22 Jul 2011 18:01:54 +0100, isabelle <i@v.org> wrote:

> hi,
>
> Sub Macro1()
> rws = Range("B65536").End(xlUp).Row
> For i = Range("B65536").End(xlUp).Row To 2 Step -1
>   If Range("B" & i - 1) <> Range("B" & i) Then
>    Rows(i & ":" & rws).Sort Key1:=Range("D" & i), Order1:=xlDescending,  
> Header:=xlNo
>    Rows(i).Insert Shift:=xlDown
>    rws = i - 1
>   End If
> Next
> End Sub
>
>

Had another go at this and it does the lot and works perfectly.

I am very grateful isabelle

It has saved me a lot of time.
0
Saxman
7/23/2011 7:01:37 AM
Reply:

Similar Artilces:

Newbie OLAP pivottable filter and sort on values
Hi, I have the simpliest of cube pivottables: Year as report filter, Products as Row labels and Amount as value. Is there a way to filter (a cube pivottable) rows on values ie "amount > 1000" or "amount <> 0" ? I only find (when rightclicking Products row label) "Filter top 10". /Jerome I use Excel2007 and AnalysisServices2005 sp3 cubes. ...

Hiding rows containing zeroes or blanks in pivot tables?
How do I hide rows containing zeroes or blanks in pivot tables? You can use programming to hide the rows with a zero total. For example: '====================================== Sub HidePivotZeroRows() 'hide worksheet rows that contain all zeros Dim rng As Range For Each rng In ActiveSheet _ .PivotTables(1).DataBodyRange.Rows If Application.Sum(rng) = 0 Then rng.EntireRow.Hidden = True Else 'unhide any previously hidden rows rng.EntireRow.Hidden = False End If Next rng End Sub '================================ Sub UnhidePivotRows() 'unhide all...

Discontinuos lines in the same series
I have 2 discontinuos ranges for a series. X: AB203:205 AB209:212 Y: AC203:205 Ac209:212 the problem is that this results in a single line while I want it to b 2 separate lines with a gap when the ranges change. Is this possible? don't want to use 2 series to achieve the same because I keep changin the range of the series using a macro. This is the only case where have a discontinuous range. Other cases all have continuous ranges. Thanks. Ra -- routera ----------------------------------------------------------------------- routeram's Profile: http://www.excelforum.com/m...

Area charts and blank cells
Hi, I'm trying to plot costs vs. time (month) on an area chart. I want to split up the costs by time, so I've created a bunch of different data series which look something like: Jan Feb Mar Apr May Jun $2 $6 $1 $1 $3 $3 $8 $4 This is basically just breaking up the original data series... ie Jan Feb Mar Apr May Jun $2 $6 $1 $3 $8 $4 I was hoping this would produce a nice, continuous area chart sectioned off by colour. The problem is that it interprets the blank spaces as zero and so I get these weird e...

Error: The node to be inserted is from a different document context
I'm getting an error that I'm very confused about. I'm trying to generate an XML file completely from scratch in code. Here is the code fragment that is causing me headaches: Dim mBOEDOC as xml.xmldocument dim inode as xml.xmlnode dim iNode2 as xml.Xmlnode mboedoc=new xml.xmldocument inode2=mboedoc.createnode(xml.XmlNodeType.Document, "BOETask", "") iNode = mBOEDoc.createnode(xml.xmlnodetype.Element, "Program","") inode2.appendchild(inode) Inode.innertext=mprogramname The error is showing up on the appendchild procedure. I'm gene...

RMS and SAGE Line 50
Does anybody use sage in conjunction with RMS, and if so what software do you use to link the 2 and how does it work? i.e. how do i reconcile an invoice with a purchase order etc. "Philip Gass" wrote: > Does anybody use sage in conjunction with RMS, and if so what software do > you use to link the 2 and how does it work? i.e. how do i reconcile an > invoice with a purchase order etc. > > Philip We can probably help. As Sage Line 50 Developers we understand the import and export of Line 50 transactions via ODBC and Sage Data Objects. Please e-mail me wi...

Sort creating total errors
I have a spreadsheet with about 20 columns and 8,000 rows. I have numerical data in most of the columns corresponding to people. Like this: John 9 6 Debbie 9 5 James 8 5 Total 26 16 If I highlight all columns and do a sort by the person's name, the total number at the bottom changes even if the data has not changed. Do you know why this is happening? Thanks in advance for any help! What are the formulas you used in those total cells? excel idiot wrote: > > I have a spreadsheet with about 20 columns and 8,000 rows. I have numerica...

Why does blank pages print inside a booklet I created in Publisher
I created a booklet in publisher and when I print the booklet everything prints out okay except for the inside of the book, a blank page prints. Why, and what can I do to fix it so the following pages can print consistently. How many pages do you have in your booklet? It needs to be a multiple of 4. -- JoAnn Paules Microsoft MVP - Publisher How to ask a question http://support.microsoft.com/kb/555375 "Publisher General Question" <Publisher General Question@discussions.microsoft.com> wrote in message news:1C503106-3A4E-4DD6-80F4-379DC1E88FEA@microsoft.com... >I c...

SOP line item status
Hi, Is there a field in the "Sales transaction amounts work" table that is updated when we post a receiving for a PO line item which is linked to SOP sales line item? Also, once an SOP order is transferred to an invoice is there a field on the "Sales transaction amounts work" or "Sales transaction amounts history" that is updated for the sales order line item? Please help. Thanks Dev The Quantity Received is updated. SOP60100, by the way, is the link between PO and SOP if you want purchase order numbers. I could not find a field in the line item tab...

coloured roes static while sorting data
I want to set up a list that has alternating coloured rows to make it more legible but I want the coloured rows to remain static while the data is sorted in various ways. Is it possible to do this? I have used the list template in excel but the gray row colour is less than ideal. I want to use a pale yellow and a pale green alternating. hi see if this site helps you out. http://www.cpearson.com/Excel/banding.aspx Regards FSt1 "ybeckett" wrote: > I want to set up a list that has alternating coloured rows to make it more > legible but I want the coloured...

Return All Record if Check Box is Blank
How would I return a subset of records if a form check box is checked but return all records if the check box is blank. The formcheck box is: [Forms]![frm_switchboard]![chk_rndm] The field [RandomMarker] can have the values of "Y" or Null only. The following SQL pulls the correct data when the check box is checked, but I recive an "OpenForm action was canceled" when the box is blank. How would I modify the query to pull all results ([RandomMarker]="Y" or Null) when the box is not checked? SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_tran...

Same series, different coloured line?
If I have a continuos data series populating a chart, is there any way of having some of the values plot in a different style line? i.e I have 24 values, half of which are extrapolated which I format in red. They are all the same series but I want the red formatted, extrapolated values to chart as a dotted line. Is there any quick way of doing this without breaking it into more than one series? Any help greatly appreciated. Thanks, Jason ...

Sorting views....what's up??????
Hello, I've seen a couple of posts on this and just cannot believe what I'm reading! We've created several custom account views (ie Active Accounts, Inactive Accounts, My Accounts, etc). We've added 10+ so far, and were planning on adding more to the drop down list. All of a sudden out of the blue this morning the views are in a completely random order. All along they've been sorted alphabetically. Now today, they're completed jumbled in the drop down and unusable. I'm reading that there is no way to resort - is this true?? And why would it all of a sudden b...

Global Signature Line
How can I assign a global signature line to all my outlook users (possilby from within Exchange 2007)? Is there a way to control this and assign to all users? See http://www.howto-outlook.com/howto/corporatesignatures.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "dswindell" <dswindell@discussions.microsoft.com> wrote in message news:CEBCEA34-6880-44D2-8A82-4A9AB5DF...

Sorting
All of a sudden, in a particular file, I find that Excel won't sort properly. I have about 15 columns, with headings, and normally if I want to sort I click in the relevant column and I get a dialog which ask me if I want to sort by ..... and it uses the column heading name, and the 'my list has a header row' defaults to 'yes'. No problem. But one file doesn't do all this. The 'my list has a header row' defaults to 'no' and the 'sort by .....' uses the column letter. Now these issues would not be a problem if I could simply click ...

Deleting the (blank) cell out of a Pivot Table
I'm trying to delete the cell that reads "(blank)" on a Pivot Table, I have tried every possible setting(except for your advice)and I cant achieve this setting. I'm trying to print the pivot table as a table and I don't want to see the "(blank)" cell written. Thank you for your help! Kevin. You could hide the (blank) entries with conditional formatting: 1. Select the cells in the pivot table 2. Choose Format>Conditional Formatting 3. Leave the first dropdown as Cell Value Is 4. From the centre dropdown, choose Equal to 5. In the text box, type (Blank) 6...

Tracking Changes Made By Sorting
I am working with a large list and would like to be able to see what changes were made when I sort the data (alphabetical sort). Can this be done without having to go through and check manually? ...

Why are all my Word documents blank?
Lately my Word docs are coming up blank, whether I open from a folder, from Word, view in print preview, etc. I can open them in WordPad and the text is displayed. But not in Word. I'm in Office 2003 on an HP Pavilion laptop. A restart solves the problem, but it keeps happening. ...

No blank in unbound text box
Hello! I have a form with an unbound text box, and then a button. You select from the dropdown (which gets its list from a query) and then push the button. What I don't want is when the form opens, and the textbox is blank, for the button to be pushable. So the user has to select something from the list first. Any ideas? Thanks! VR/Lost On Wed, 6 Jan 2010 17:57:06 -0800 (PST), Lostguy <cpocpo@mail.com> wrote: >Hello! > >I have a form with an unbound text box, and then a button. You select >from the dropdown (which gets its list from a query) and th...

Print Range Lines
Is there any way to turn off the dotted lines that show the print range area within worksheets? I don't know why someone would want them unavailable, but a co-worker of mine insists "they don't turn on half of the time anyway, so why use/show them at all?" We use Excel 2003. Thanks. Tools>options>view and under window options deselect page breaks -- Regards, Peo Sjoblom "Me" <Me@discussions.microsoft.com> wrote in message news:B194547E-DE11-4508-97E2-52F99AF9F096@microsoft.com... > Is there any way to turn off the dotted lines that show ...

Excel Scatter Plot with connected by smooth lines
It is not honoring my numbers for the X-Axis, they go from -1300 to 3200, on the graph it's going from -8900 to -8200, but when i go to the source data my numbers for the X-Axis are selected how do i change this? Its urgent!!! Double click on the x axis and on the Scale tab manually select minimum and maximum for the scale. If you inserted the graph, instead of changing data on an existing graph, then there is something else going on, since by default Excel will encompass the entire range. You might also select the chart and on the menu select Chat|Chart type to ensure that you ha...

Paste Special Skip Blanks not skipping blanks, but overwriting...
Using Excel 2003, I'm having problems getting the "skip blanks" feature to work. It works on one computer using 2003, but not on another. I updated, but still have the problem. ...

Changing CListCtrl Report View Line Height
Is it possible to change the height of a line in the CListCtrl report view? If so, how? I don't need to make each line a different height (although that would be nice); just changing the global height would be sufficient. Thanks. I don't know how to do it directly, but I know of a "hack" that should work. The ClistCtrl adjusts to height of a row so as to accommodate an image at the head of the row. So, call SetImageList and pass in a CImageList that contains a transparent image of the desired height, and maybe only one pixel wide so that there's not an unnatural spa...

How do I lock a row in place so that when I sort the worksheet, t.
How do I lock a row in place so that when I sort the worksheet, the row stays at its location, and the other rows sort around it? I believe you can use Data/Filter. Ideally there is already some column in the worksheet that will show all of the records but that row (maybe a helper column would be necessary). Then sort the data and set the filter to show all records. "primenumber" <primenumber@discussions.microsoft.com> wrote in message news:4CD4DE06-48C8-4650-9190-E42BB641A689@microsoft.com... > How do I lock a row in place so that when I sort the worksheet, the r...

blank cells being counted ??
Wondering if someone could help with this problem After doing a vlookup and then cleaning up with pastespecial/values I am finding that the visually empty cells are still being counted in my counta totals I have tried this subroutine to clearcontents on the empty cells, which works if I do it manually. Sub clearempty() For i = 3 To 588 If Cells(i, 14) = "" Then ActiveCell.clearcontents End If Next i End Sub Can anyone tell me 1. why my clearcontents subrouting is not clearing and 2. is there another way to clear out this invisible data i...