I am in need of some help. I am new to VBA programming and in need to
find a way to copy a value from column D and paste it in column E in
the blank cells between two values in the column. I am also trying to
delete columns 5 & 6 after I copy and paste the values. The row
deletion works by itself, the trouble is pasting in the blanks cells.
Here is the code I have - what am I doing wrong?

Code:
--------------------

Sub Delete_Empty_Rows()
Dim i As Long
Dim c As Range
Dim rSearchRange As Range
For i = rSearchRange.Row.Count To 1 Step -1
Set c = Worksheets("VALUES").UsedRange.Columns(4).Find("So rt Program:", LookIn:=xlValues)
If WorksheetFunction.CountIf(c) Then
c.SpecialCells(xlCellTypeConstants).Copy
If WorksheetFunction.CountBlank(c) Then
c.SpecialCells(xlCellTypeConstants).PasteSpecial
Next i

Set rSearchRange = Worksheets("VALUES").UsedRange.Columns(6) 'for example
If WorksheetFunction.CountBlank(rSearchRange) Then _
rSearchRange.SpecialCells(xlCellTypeBlanks).Entire Row.Delete
Set rSearchRange = Worksheets("VALUES").UsedRange.Columns(5) 'for example
If WorksheetFunction.CountBlank(rSearchRange) Then _
rSearchRange.SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--------------------


--
3Sixty
------------------------------------------------------------------------
3Sixty's Profile: http://www.excelforum.com/member.php...o&userid=28601
View this thread: http://www.excelforum.com/showthread...hreadid=482652

I haven't worked through all the code but you seem to be refering to the
range rSearchRange before you set it to anything:

Sub Delete_Empty_Rows()
Dim i As Long
Dim c As Range
Dim rSearchRange As Range
For i = rSearchRange.Row.Count To 1 Step -1
...

Hope this helps
Rowan

3Sixty wrote:
I am in need of some help. I am new to VBA programming and in need to
find a way to copy a value from column D and paste it in column E in
the blank cells between two values in the column. I am also trying to
delete columns 5 & 6 after I copy and paste the values. The row
deletion works by itself, the trouble is pasting in the blanks cells.
Here is the code I have - what am I doing wrong?

Code:
--------------------

Sub Delete_Empty_Rows()
Dim i As Long
Dim c As Range
Dim rSearchRange As Range
For i = rSearchRange.Row.Count To 1 Step -1
Set c = Worksheets("VALUES").UsedRange.Columns(4).Find("So rt Program:", LookIn:=xlValues)
If WorksheetFunction.CountIf(c) Then
c.SpecialCells(xlCellTypeConstants).Copy
If WorksheetFunction.CountBlank(c) Then
c.SpecialCells(xlCellTypeConstants).PasteSpecial
Next i

Set rSearchRange = Worksheets("VALUES").UsedRange.Columns(6) 'for example
If WorksheetFunction.CountBlank(rSearchRange) Then _
rSearchRange.SpecialCells(xlCellTypeBlanks).Entire Row.Delete
Set rSearchRange = Worksheets("VALUES").UsedRange.Columns(5) 'for example
If WorksheetFunction.CountBlank(rSearchRange) Then _
rSearchRange.SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--------------------