Insert Blank row between rows with values

[ioncila]

Hi

If I have several rows with values, how do I insert a blank row between each two?

The same way, If I have in the same list two rows with values with more than one blank row, How do I delete them and leave just one?

Thanks

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If Application.CountA(.Rows(i - 1)) = 0 Then

If Application.CountA(Rows(i)) = 0 Then

.Rows(i).Delete
End If
Else

If Application.CountA(Rows(i)) <> 0 Then

.Rows(i).Insert
End If
End If
Next i
End With

End Sub
[/vba]

[RolfJ]

Here are two macros that might do the job for you:

[vba]
Const NROWS_WITHOUTBLANK = 3

Sub InsertBlankRows()
Dim db As Range
Set db = ActiveSheet.UsedRange

Dim iRow As Integer
Dim rowCount As Integer
iRow = 0
Do
If rowCount = NROWS_WITHOUTBLANK Then
db.Rows(iRow + 1).EntireRow.Insert
rowCount = 0
iRow = iRow + 1
Set db = ActiveSheet.UsedRange
Else
rowCount = rowCount + 1
iRow = iRow + 1
End If
If iRow > db.Rows.Count Then Exit Do
Loop
End Sub

Sub DeleteExtraBlankRows()
Dim db As Range
Set db = ActiveSheet.UsedRange
Dim iRow As Integer
For iRow = db.Rows.Count To 2 Step -1
Dim emptyCells As Range
Dim emptyCellsPreviousRow As Range
On Error Resume Next
Set emptyCells = db.Rows(iRow).SpecialCells(xlCellTypeBlanks)
Set emptyCellsPreviousRow = db.Rows(iRow - 1).SpecialCells(xlCellTypeBlanks)
If Not emptyCells Is Nothing And Not emptyCellsPreviousRow Is Nothing Then
If emptyCells.Columns.Count = db.Columns.Count And emptyCellsPreviousRow.Columns.Count = db.Columns.Count Then
db.Rows(iRow).EntireRow.Delete
Set emptyCells = Nothing
Set emptyCellsPreviousRow = Nothing
End If
End If
Next iRow
End Sub

[/vba]

[ioncila]

Thank you so much for your help @xld and @rolfj.

A little feedback:

@xld
It works perfectly if there is no blank rows between data rows. If there is at least one blank row in between, macro adds one more blank for each existing blank instead of deleting those which are surplus.

@rolj
It works perfectly. The "but" issue is to use 2 macros instead of one.

Both are a little slower but that is understandable, specially in files with hundreds of data rows.

Anyways, that is a great help.

Thanks

[Bob Phillips]

@xld
It works perfectly if there is no blank rows between data rows. If there is at least one blank row in between, macro adds one more blank for each existing blank instead of deleting those which are surplus.

Are you sure? I tested it, and just tried it again, and I don't get that.

[ioncila]

Are you sure? I tested it, and just tried it again, and I don't get that.

Hi
I owe you an apology, your code now works perfectly.
But this is a mistery:

When I tested first (at work - in excel 2003), I have copied your code and rolfj's to 2 different modules and tried one at a time, several times. And the result was the one I have mentioned then.

Now (at home - in excel 2007) I tested just yours, and as I said that works perfectly.

Tomorrow I will try it again in excel 2003 version, just to comprove it.

But is there some issue relative to these aspects? Or am I running over a mistake of mine?

Thanks

[vlazav]

You may also try using Shift-Space, Ctrl-'+' for adding and Shift-Space, Ctrl-'-' for deleting.