Making my list look better-want blank separator row

[Guest]

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 !

 

[kyle775]

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

 

[Gord Dibben]

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

 

[Sandy Mann]

Jugglertwo,

What differentiates one department from another?

--
Regards,

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

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]

Good to hear.

You can record yourself selecting Column A and F5>Special>Blanks>OK

Edit>Delete>Entire 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

 

[Guest]

Mucho gracias!
I owe you one (make that two

Jugglertwo

 

[Gord Dibben]

Thanks for the feedback.

Gord

Mucho gracias!
I owe you one (make that two

Jugglertwo