Blank rows in a filtered list
 

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

--
carg
-----------------------------------------------------------------------
carg1's Profile: http://www.excelforum.com/member.php...fo&userid=1527
View this thread: http://www.excelforum.com/showthread.php?threadid=26895

Blank rows in a filtered list
 

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.

carg1 wrote:

Hi, I was wondering if anyone could help me with this. I have a sheet
with columns A:I containing data. In column D, there are numbers, the
majority of which repeat. I wanted to "group" those together by
inserting blank rows after each group, for example, if there is a group
of 12 consecutive rows in which the number 58496351 is in column D, I
want a blank row inserted after the last row which contains that
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 this
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 is
not hidden then it inserts a blank row after it. I don't know if
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 a
loop in it. I tried a Do While...Loop Until and it gives me an "Object
required" error. So how do I get this thing to loop, or is there a
better way to do this? :confused:

--
carg1
------------------------------------------------------------------------
carg1's Profile: http://www.excelforum.com/member.php...o&userid=15271
View this thread: http://www.excelforum.com/showthread...hreadid=268955

--

Dave Peterson