Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Blank rows in a filtered list
Its definitely an idea, I just tried it, and I was able to achieve m effect. I just autofiltered for blanks on another column, deleted th contents of all the rows that had the subtotals, and viola, blank rows Thanks! Dave Peterson Wrote: 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 line 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 ke column.) Remember to have your data sorted the way you want before you d data|subtotals. carg1 wrote: Hi, I was wondering if anyone could help me with this. I have 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 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 ro 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 implemen a loop in it. I tried a Do While...Loop Until and it gives me a "Object required" error. So how do I get this thing to loop, or is there a better way to do this? -- 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 -- carg ----------------------------------------------------------------------- carg1's Profile: http://www.excelforum.com/member.php...fo&userid=1527 View this thread: http://www.excelforum.com/showthread.php?threadid=26895 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy to filtered blank rows from another spreadsheet | Excel Discussion (Misc queries) | |||
How do I count rows in a filtered list when using AutoFilter? | Excel Worksheet Functions | |||
Define a range containing the first 10 rows of a filtered list | Excel Worksheet Functions | |||
Blank rows in a filtered list | Excel Programming | |||
colour alternate rows in filtered list | Excel Programming |