Can I develop a macro to add blank rows to a list in Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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