Blank rows

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Excel 2000

I've tried several different ways, including the subtotal
function (this creates even more rows with information
which is confusing). I'm trying to make this simple to
use for other people by creating a form box with a macro
to do the function. But here is what I want to happen. I
have a spreadsheet with different sections that must be
seperated by a blank row. Also, the totals for each
section have to appear at the bottom with a blank row
seperating the last of the sections with the totals. Each
section has no more than 10 rows of data.

example:

col a col b col c
john miami 2
bill miami 3
henry miami 2
(blank row)
joe pittsburgh 4
todd pittsburgh 3
(blank row)
jason chicago 2
tracy chicago 4
(blank row)
miami totals 7
pittsburgh totals 7
chicago totals 6

Any help?

Thank you,
Greg Malenky
 
I don't think I'd actually insert rows. I like to just change the rowheight to
give the appearance of double spacing. But this seemed to work ok for me:

Option Explicit
Sub testme()

Dim myRng As Range
Dim LastRow As Long
Dim wks As Worksheet
Dim iRow As Long

Set wks = ActiveSheet

With wks

.Range("A:C").Sort Key1:=.Range("B1"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

Set myRng = .Range("B1:B" & LastRow)

myRng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Cells(LastRow + 2, "A"), Unique:=True

.Range("B" & LastRow + 3 & ":b" _
& .Cells(.Rows.Count, "A").End(xlUp).Row).FormulaR1C1 _
= "=sumif(" & myRng.Offset(1, 0).Resize(myRng.Rows.Count - 1) _
.Address(ReferenceStyle:=xlR1C1) & _
",rc[-1]," & _
myRng.Offset(1, 1).Resize(myRng.Rows.Count - 1) _
.Address(ReferenceStyle:=xlR1C1) & ")"

.Rows(LastRow + 2).Delete

For iRow = LastRow To 3 Step -1
If .Cells(iRow, "B").Value <> .Cells(iRow - 1, "B").Value Then
.Rows(iRow).Insert
End If
Next iRow

End With
End Sub

I expect a header row in row 1. This sorts the data to start, then uses
Data|Advanced filter to extract the unique entries, then adds a formula next to
those entries to sum them up.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top