This should be all of the code you will need. You can modify teh number of
inserted cells by changing the constant at the top.

Option Explicit
Private Const INSERT_ROWS As Integer = 200

Sub DeleteBlanks()
Dim rngBlanks As Range
Dim wks As Worksheet

Set wks = ActiveSheet
On Error Resume Next
Set rngBlanks = wks.Columns("D").SpecialCells(xlBlanks)
On Error GoTo 0

If Not rngBlanks Is Nothing Then rngBlanks.EntireRow.Delete
End Sub

Sub InsertRows()
Dim rng As Range
Dim wks As Worksheet
Set wks = ActiveSheet

With wks
Set rng = .Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0)
Do While rng.Row = 2
.Range(rng.Offset(1, 0), rng.Offset(1 + INSERT_ROWS,
0)).EntireRow.Insert
Set rng = rng.Offset(-1, 0)
Loop
.Range(rng.Offset(1, 0), rng.Offset(1 + INSERT_ROWS, 0)).EntireRow.Insert
End With
End Sub

--
HTH...

Jim Thomlinson


"Chris_t_2k5" wrote:

Thanks for that. the seccond part of my problem is if I have values in 20
cells for example A1:A20. How can i insert blank rows (for example 200)
between each of these values. so there will be a value in A1, 200 blank rows,
a value in A201, 200 blank rows etc...

Thanks

"Jim Thomlinson" wrote:

This will delete the blanks where column D is blank (works for however many
rows you have). I am not sure I understand the second part completely
though...
--
HTH...

Jim Thomlinson


"Chris_t_2k5" wrote:

Here is my problem. I have data for example C1:D200.

All of C1:C200 have values in but only certain cells in D1:D200 have values
in them.

Is it possible to create a macro that will delete the rows for a certain
selection where D have no values in?

Also - after this has been done and there are say 20 values in each column
of C1:D20 is it possible to insert say 200 blank rows after each value. (eg
C1 has value, D1 has value, C201 has value, D201 has value etc...)

Thanks