Copy Row to Below Blank Row

[danjw_98]

I am trying to copy a row to the below blank row. I have the below macro that works for cells but can't seem to modify to copy the entire row. any help would be appreciated...

ub FillBlankCellsFromCellAbove()
Application.ScreenUpdating = False
Dim cell As Object
Set rng = Range("a3:a" & Range("b65536").End(xlUp).Row)
For Each cell In rng

If cell.Value = "" Then
cell.Value = cell.Offset(-1, 0).Value
r1 = cell.Value
cell.Value = r1
Else: End If
Next cell
Application.ScreenUpdating = True
End Sub

 

[John Davis]

I am trying to copy a row to the below blank row. I have the below macro that works for cells but can't seem to modify to copy the entire row. any help would be appreciated...

ub FillBlankCellsFromCellAbove()
Application.ScreenUpdating = False
Dim cell As Object
Set rng = Range("a3:a" & Range("b65536").End(xlUp).Row)
For Each cell In rng

If cell.Value = "" Then
cell.Value = cell.Offset(-1, 0).Value
r1 = cell.Value
cell.Value = r1
Else: End If
Next cell
Application.ScreenUpdating = True
End Sub

Would this be it?

Sub FillBlankCellsFromCellAbove()
Application.ScreenUpdating = False
Dim cell As Range
Set rng = Range("a3:a" & Range("b65536").End(xlUp).Row)
For Each cell In rng

If cell.Value = "" Then
cell.EntireRow.Value = cell.EntireRow.Offset(-1, 0).Value
r1 = cell.Value
cell.Value = r1
Else: End If
Next cell
Application.ScreenUpdating = True
End Sub

 

[danjw_98]

thanks...
I had put together the below macro that seems to work but did some help with this. In column g i have a date and when i insert/copy this to a blank row below i want to subtract the date by one day. any assistance would be appreciated.


Sub CopyRowAbovePasteBelowBlankRow()
Application.ScreenUpdating = False
Set rng = Range("a2:a" & Range("b65536").End(xlUp).Row)
For Each cell In rng
If cell.Value = Empty Then
cell.Offset(-1, 0).EntireRow.Copy
cell.Offset(0, 0).EntireRow.Select
ActiveSheet.Paste
cell.Offset(0, 5).Value = "In Progress"
ActiveCell.Offset(0, 5).Interior.ColorIndex = 42

'subtract date by one day
val1 = ActiveCell.Offset(0, 6).Value
val2 = val1 - 1
ActiveCell.Offset(0, 6).Value = val2

cell.Offset(1, 0).EntireRow.Select
End If
Next cell
End Sub

 

[John Davis]

thanks...
I had put together the below macro that seems to work but did some help with this. In column g i have a date and when i insert/copy this to a blank row below i want to subtract the date by one day. any assistance would be appreciated.


Sub CopyRowAbovePasteBelowBlankRow()
Application.ScreenUpdating = False
Set rng = Range("a2:a" & Range("b65536").End(xlUp).Row)
For Each cell In rng
If cell.Value = Empty Then
cell.Offset(-1, 0).EntireRow.Copy
cell.Offset(0, 0).EntireRow.Select
ActiveSheet.Paste
cell.Offset(0, 5).Value = "In Progress"
ActiveCell.Offset(0, 5).Interior.ColorIndex = 42

'subtract date by one day
val1 = ActiveCell.Offset(0, 6).Value
val2 = val1 - 1
ActiveCell.Offset(0, 6).Value = val2

cell.Offset(1, 0).EntireRow.Select
End If
Next cell
End Sub

Does the code you have not work for the date part?

Why not:

ActiveCell.Offset(0,6).Value = ActiveCell.Offset(0,6).Value - 1

 

[danjw_98]

that's correct the portion of the code that is not working is the date.

'subtract date by one day
val1 = ActiveCell.Offset(0, 6).Value
val2 = val1 - 1
ActiveCell.Offset(0, 6).Value = val2

 

[John Davis]

that's correct the portion of the code that is not working is the date.

'subtract date by one day
val1 = ActiveCell.Offset(0, 6).Value
val2 = val1 - 1
ActiveCell.Offset(0, 6).Value = val2

Did you try it with the suggested change?

 

[danjw_98]

I just tired the below and unfortunately did not work.

ActiveCell.Offset(0,6).Value = ActiveCell.Offset(0,6).Value - 1

it just put a -1 in the cell

 

[John Davis]

I just tired the below and unfortunately did not work.

ActiveCell.Offset(0,6).Value = ActiveCell.Offset(0,6).Value - 1

it just put a -1 in the cell

That line - when I isolate it and test it, it works. It only gives me the return you're getting when the value of the offset cell is 0.