Delete blank rows in selection
 

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

Delete blank rows in selection
 

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

Delete blank rows in selection
 

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


--


Gary


"Chris_t_2k5" wrote in message
...
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

Delete blank rows in selection
 

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