Delete blank rows in selection

[Guest]

Here is my problem. I have data for example C1200.

All of C1:C200 have values in but only certain cells in D1200 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 C120 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

 

[Guest]

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

 

[Gary Keramidas]

maybe something like this

Option Explicit
Dim cell As Range
Dim i As Long
Sub ins()

For i = 20 To 1 Step -1
Range("A" & i).EntireRow.Resize(200).Insert
Next
End Sub

 

[Guest]

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