I have a list that my boss wants to look "pretty." The boss really wants a
blank row after each of the various departments so that it is easier to read.
We have various co-workers and we frequently sort by dept. It is easier to
read with a blank row after each dept.

is there an easy way to have this happen?
I'm familiar with conditional formatting but not sure if I can get this
exact look.
Also, I'm playing with using the subtotalling feature and then using some
conditional formatting. This seems to work but maybe there is a quick way to
make this happen.

Any suggestions would be greatly appreciated !

On Mar 19, 1:05 pm, Jugglertwo
wrote:
I have a list that my boss wants to look "pretty." The boss really wants a
blank row after each of the various departments so that it is easier to read.
We have various co-workers and we frequently sort by dept. It is easier to
read with a blank row after each dept.

is there an easy way to have this happen?
I'm familiar with conditional formatting but not sure if I can get this
exact look.
Also, I'm playing with using the subtotalling feature and then using some
conditional formatting. This seems to work but maybe there is a quick way to
make this happen.

Any suggestions would be greatly appreciated !

Pivot Table

Formulas or Conditional Formatting cannot insert a row.

To actually insert a row at each change in Dept. would require a macro.

This one inserts a row at each change in Column A

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 19 Mar 2007 10:05:15 -0700, Jugglertwo
wrote:

I have a list that my boss wants to look "pretty." The boss really wants a
blank row after each of the various departments so that it is easier to read.
We have various co-workers and we frequently sort by dept. It is easier to
read with a blank row after each dept.

is there an easy way to have this happen?
I'm familiar with conditional formatting but not sure if I can get this
exact look.
Also, I'm playing with using the subtotalling feature and then using some
conditional formatting. This seems to work but maybe there is a quick way to
make this happen.

Any suggestions would be greatly appreciated !

Jugglertwo,

What differentiates one department from another?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Jugglertwo" wrote in message
...
I have a list that my boss wants to look "pretty." The boss really wants a
blank row after each of the various departments so that it is easier to
read.
We have various co-workers and we frequently sort by dept. It is easier to
read with a blank row after each dept.

is there an easy way to have this happen?
I'm familiar with conditional formatting but not sure if I can get this
exact look.
Also, I'm playing with using the subtotalling feature and then using some
conditional formatting. This seems to work but maybe there is a quick way
to
make this happen.

Any suggestions would be greatly appreciated !

I tried your macro code and it worked perfectly. Thanks!
Would it be possible for you to provide me the code to remove the blank
lines after the first macro has run?
Either way, thanks for your assistance!
It is appreciated !
Jugglertwo

"Gord Dibben" wrote:

Formulas or Conditional Formatting cannot insert a row.

To actually insert a row at each change in Dept. would require a macro.

This one inserts a row at each change in Column A

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 19 Mar 2007 10:05:15 -0700, Jugglertwo
wrote:

I have a list that my boss wants to look "pretty." The boss really wants a
blank row after each of the various departments so that it is easier to read.
We have various co-workers and we frequently sort by dept. It is easier to
read with a blank row after each dept.

is there an easy way to have this happen?
I'm familiar with conditional formatting but not sure if I can get this
exact look.
Also, I'm playing with using the subtotalling feature and then using some
conditional formatting. This seems to work but maybe there is a quick way to
make this happen.

Any suggestions would be greatly appreciated !

Good to hear.

You can record yourself selecting Column A and F5SpecialBlanksOK

EditDeleteEntire Rows

Or what the heck............. just use this one.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord


On Mon, 19 Mar 2007 15:04:08 -0700, Jugglertwo
wrote:

I tried your macro code and it worked perfectly. Thanks!
Would it be possible for you to provide me the code to remove the blank
lines after the first macro has run?
Either way, thanks for your assistance!
It is appreciated !
Jugglertwo

"Gord Dibben" wrote:

Formulas or Conditional Formatting cannot insert a row.

To actually insert a row at each change in Dept. would require a macro.

This one inserts a row at each change in Column A

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 19 Mar 2007 10:05:15 -0700, Jugglertwo
wrote:

I have a list that my boss wants to look "pretty." The boss really wants a
blank row after each of the various departments so that it is easier to read.
We have various co-workers and we frequently sort by dept. It is easier to
read with a blank row after each dept.

is there an easy way to have this happen?
I'm familiar with conditional formatting but not sure if I can get this
exact look.
Also, I'm playing with using the subtotalling feature and then using some
conditional formatting. This seems to work but maybe there is a quick way to
make this happen.

Any suggestions would be greatly appreciated !

Mucho gracias!
I owe you one (make that two:)

Jugglertwo

"Gord Dibben" wrote:

Good to hear.

You can record yourself selecting Column A and F5SpecialBlanksOK

EditDeleteEntire Rows

Or what the heck............. just use this one.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord


On Mon, 19 Mar 2007 15:04:08 -0700, Jugglertwo
wrote:

I tried your macro code and it worked perfectly. Thanks!
Would it be possible for you to provide me the code to remove the blank
lines after the first macro has run?
Either way, thanks for your assistance!
It is appreciated !
Jugglertwo

"Gord Dibben" wrote:

Formulas or Conditional Formatting cannot insert a row.

To actually insert a row at each change in Dept. would require a macro.

This one inserts a row at each change in Column A

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 19 Mar 2007 10:05:15 -0700, Jugglertwo
wrote:

I have a list that my boss wants to look "pretty." The boss really wants a
blank row after each of the various departments so that it is easier to read.
We have various co-workers and we frequently sort by dept. It is easier to
read with a blank row after each dept.

is there an easy way to have this happen?
I'm familiar with conditional formatting but not sure if I can get this
exact look.
Also, I'm playing with using the subtotalling feature and then using some
conditional formatting. This seems to work but maybe there is a quick way to
make this happen.

Any suggestions would be greatly appreciated !

Thanks for the feedback.

Gord

On Mon, 19 Mar 2007 17:18:08 -0700, Jugglertwo
wrote:

Mucho gracias!
I owe you one (make that two:)

Jugglertwo

"Gord Dibben" wrote:

Good to hear.

You can record yourself selecting Column A and F5SpecialBlanksOK

EditDeleteEntire Rows

Or what the heck............. just use this one.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord


On Mon, 19 Mar 2007 15:04:08 -0700, Jugglertwo
wrote:

I tried your macro code and it worked perfectly. Thanks!
Would it be possible for you to provide me the code to remove the blank
lines after the first macro has run?
Either way, thanks for your assistance!
It is appreciated !
Jugglertwo

"Gord Dibben" wrote:

Formulas or Conditional Formatting cannot insert a row.

To actually insert a row at each change in Dept. would require a macro.

This one inserts a row at each change in Column A

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 19 Mar 2007 10:05:15 -0700, Jugglertwo
wrote:

I have a list that my boss wants to look "pretty." The boss really wants a
blank row after each of the various departments so that it is easier to read.
We have various co-workers and we frequently sort by dept. It is easier to
read with a blank row after each dept.

is there an easy way to have this happen?
I'm familiar with conditional formatting but not sure if I can get this
exact look.
Also, I'm playing with using the subtotalling feature and then using some
conditional formatting. This seems to work but maybe there is a quick way to
make this happen.

Any suggestions would be greatly appreciated !