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?
|