Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I develop a macro to add blank rows to a list in Excel?
I have a list containing thousands of rows to which I would like to add blank
rows between varied groups of rows. I've tried developing macros to make quick work of this tedious job but I don't know the statement I need to develop to work similiar to a subtotal action. The point at which I would like to add a row is when there is a change in a certain column. I do not need a subtotal but I do need a blank line added at every change. Is this even possible or do I add each blank line manually using shortcuts on my keyboard? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I develop a macro to add blank rows to a list in Excel?
The subroutine that follows assumes that the data you are working with resides in Sheet1, that the values that determine line insertions are in column H and that the data has been previously sorted by column H Sub InsertLine() Dim wb As Workbook Dim ws As Worksheet Dim varVal1 As Variant Dim varVal2 As Variant Set wb = ActiveWorkbook Set ws = wb.Worksheets("Sheet1") ws.Activate Range("H1").Select varVal1 = ActiveCell.Value varVal2 = ActiveCell.Offset(1).Value Do Until varVal1 = "" If varVal1 < varVal2 Then ActiveCell.Offset(1).Select Selection.EntireRow.Insert End If ActiveCell.Offset(1).Select varVal1 = ActiveCell.Value varVal2 = ActiveCell.Offset(1).Value Loop Set wb = Nothing Set ws = Nothing End Sub -- Kevin Backmann "csimont" wrote: I have a list containing thousands of rows to which I would like to add blank rows between varied groups of rows. I've tried developing macros to make quick work of this tedious job but I don't know the statement I need to develop to work similiar to a subtotal action. The point at which I would like to add a row is when there is a change in a certain column. I do not need a subtotal but I do need a blank line added at every change. Is this even possible or do I add each blank line manually using shortcuts on my keyboard? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting blank rows in Excel. | Excel Discussion (Misc queries) | |||
Deleting rows in a macro in Excel | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Conditional list without blank rows | Excel Worksheet Functions | |||
How do I remove blank rows in Excel? | Excel Discussion (Misc queries) |