Blank rows in a filtered list

  • Thread starter Thread starter carg1
  • Start date Start date
C

carg1

Hi, I was wondering if anyone could help me with this. I have a shee
with columns A:I containing data. In column D, there are numbers, th
majority of which repeat. I wanted to "group" those together b
inserting blank rows after each group, for example, if there is a grou
of 12 consecutive rows in which the number 58496351 is in column D,
want a blank row inserted after the last row which contains tha
number; another group of 2 rows in which 9633698714 is in column D
blank row, and so forth. So far the closest I've come is with thi
macro:

Sub Insert_Blank_Rows()

Columns("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Selection.End(xlDown).Select

If Selection.Rows.Hidden = False Then
ActiveCell.EntireRow.Insert shift:=xlUp
ActiveCell.Offset(-1, 0).Select
End If
End Sub

It does an advanced filter on column d, then, presumably, if the row i
not hidden then it inserts a blank row after it. I don't know i
there's a more efficient way to do it, I'd like to know if there is.
But the thing with that macro is that I'm not sure how to implement
loop in it. I tried a Do While...Loop Until and it gives me an "Objec
required" error. So how do I get this thing to loop, or is there
better way to do this? :confused
 
Have you tried the built in Data|subtotals.

The new rows may not be blank, but you could clear them if you want.

Using the outline symbols at the right, just show the subtotal lines that you
want to clear.

Select your range
edit|goto|special|blanks
and hit the delete key on the keyboard.

You can turn off the outlining symbols via:
tools|options|view tab

(but I like those subtotals--even if it's just a count of the key column.)

Remember to have your data sorted the way you want before you do data|subtotals.
 
Back
Top