Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a blank row between values
I would like to be able to automate the row adds.
rows 1-3 are alike, rows 4-9 are alike, row 10 is alone, rows 11-15 are alike. How do I add blank rows between these groups of varying values without selecting the top row in each value and performing an insert row? some of these sheets have hundreds of values. i.e. what i have: 001 001 002 002 002 003 003 003 003 What I want: 001 001 002 002 002 003 003 003 003 etc... thanks in advance Rick |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a blank row between values
Sub test()
c = 1 ' for column A For r = 1000 To 2 Step -1 ' for data starting at row 1 and ending at row 1000 If Cells(r, c).Value < Cells(r - 1, c).Value Then Rows(r).Insert r = r - 1 End If Next r End Sub -- Ian -- "Rick" wrote in message ... I would like to be able to automate the row adds. rows 1-3 are alike, rows 4-9 are alike, row 10 is alone, rows 11-15 are alike. How do I add blank rows between these groups of varying values without selecting the top row in each value and performing an insert row? some of these sheets have hundreds of values. i.e. what i have: 001 001 002 002 002 003 003 003 003 What I want: 001 001 002 002 002 003 003 003 003 etc... thanks in advance Rick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a blank row between values
Excel has a feature data|subtotals that does close to what you want. It also
provides the ability to sum/count (some kind of summary) for fields in that group. If you've tried that and don't like it, ... Just a suggestion, I wouldn't insert an empty row between the groups. It can make other things more difficult to do (charting/sorting/filtering/pivottabling(?)). I'd just increase the rowheight so that the next group looks double spaced. Option Explicit Sub testme01() Dim LastRow As Long Dim iRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row For iRow = LastRow To 2 Step -1 If .Cells(iRow, "a").Value < .Cells(iRow - 1, "a").Value Then .Rows(iRow).RowHeight = .Rows(iRow).RowHeight * 2 '.Rows(iRow).Insert End If Next iRow End With End Sub If you really want to insert the row, then delete the .rowheight line and use the .insert line (remove the apostrophe that makes it a comment. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Rick wrote: I would like to be able to automate the row adds. rows 1-3 are alike, rows 4-9 are alike, row 10 is alone, rows 11-15 are alike. How do I add blank rows between these groups of varying values without selecting the top row in each value and performing an insert row? some of these sheets have hundreds of values. i.e. what i have: 001 001 002 002 002 003 003 003 003 What I want: 001 001 002 002 002 003 003 003 003 etc... thanks in advance Rick -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a blank row after a new record changes in excel | Excel Worksheet Functions | |||
Adding Blank Cells ? | New Users to Excel | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions | |||
Adding a value to blank cells | Excel Discussion (Misc queries) |