Macro to replace missing data from adjoining column

[Woofy_McWoof_Woof]

Hi, I'm working on a spread sheet that has 6 columns - Date, Half Hour, Actual Data and Date Half Hour and Estimated Data. There are occasions when the actual data is missing (blank cell), if this happens then I need to replace the Actual Data blank cell with the corresponding value from the Estimate Data cell. This would need to align with both the date and time in order for it to be synced correctly. There is an added complication in that the time for the actual data may not align with the half hour exactly (see example below where it is 07:42, in this case it would need to align to the half hour preceding the time i.e. 07:30.

In the example below I have copied in some data with the missing periods for the actual data, these would need to be replaced by the data in the estimated data column. I would also need to run a macro that would stop once it reaches the end of the data (there could be thousands of rows to search through).

I would appreciate any help with this as its driving me crazy. I could do it with a formula easily enough but it would take up too much memory and slow it all down (especially if I have a few years worth of data).

Thanks for your help

Date	Time	Actual Data		Date	Time	Estimated Data
Sun 01-Jan-17	00:30	0.64		Sun 01-Jan-17	00:30	0.64
Sun 01-Jan-17	01:00	0.67		Sun 01-Jan-17	01:00	0.67
Sun 01-Jan-17	01:30	0.67		Sun 01-Jan-17	01:30	0.67
Sun 01-Jan-17	02:00	0.72		Sun 01-Jan-17	02:00	0.72
Sun 01-Jan-17	02:30	0.82		Sun 01-Jan-17	02:30	0.82
Sun 01-Jan-17	03:00	0.72		Sun 01-Jan-17	03:00	0.72
Sun 01-Jan-17	03:30			Sun 01-Jan-17	03:30	0.71
Sun 01-Jan-17	04:00			Sun 01-Jan-17	04:00	0.71
Sun 01-Jan-17	04:30			Sun 01-Jan-17	04:30	0.71
Sun 01-Jan-17	05:00			Sun 01-Jan-17	05:00	0.71
Sun 01-Jan-17	05:30	1.08		Sun 01-Jan-17	05:30	1.08
Sun 01-Jan-17	06:00	0.44		Sun 01-Jan-17	06:00	0.44
Sun 01-Jan-17	06:30	0.56		Sun 01-Jan-17	06:30	0.56
Sun 01-Jan-17	07:00	0.56		Sun 01-Jan-17	07:00	0.56
Sun 01-Jan-17	07:42	0.51		Sun 01-Jan-17	07:30	0.51
Sun 01-Jan-17	08:00	0.61		Sun 01-Jan-17	08:00	0.61
Sun 01-Jan-17	08:30	0.59		Sun 01-Jan-17	08:30	0.59
Sun 01-Jan-17	09:00	0.61		Sun 01-Jan-17	09:00	0.61
Sun 01-Jan-17	09:30	0.61		Sun 01-Jan-17	09:30	0.61
Sun 01-Jan-17	10:00	0.74		Sun 01-Jan-17	10:00	0.74

<tbody>
</tbody>

 

[Fluff]

Hia
Give this a go

Sub ReplaceData()
' woofy

    Dim Usdrws As Long
    Dim Cl As Range

    Usdrws = Range("A" & Rows.Count).End(xlUp).Row
    
    For Each Cl In Range("C2:C" & Usdrws)
        If Cl.Value = "" Then Cl.Value = Cl.Offset(, 3).Value
    Next Cl

End Sub

 

[Woofy_McWoof_Woof]

It worked a treat, thanks Fluff

 

[Fluff]

Glad to help & thanks for the feedback

 

[hiker95]

Woofy_McWoof_Woof,

Here is another macro for you to consider, that does not do any looping in the active worksheet, thru the rows in column C, and, should be very fast.

Sub ReplaceMissingData()
' hiker95, 08/24/2017, ME1020214
Application.ScreenUpdating = False
Dim lr As Long
With ActiveSheet
  lr = .Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  On Error Resume Next
  With .Range("C2:C" & lr)
    .SpecialCells(xlCellTypeBlanks).Formula = "=RC[3]"
    .Value = .Value
  End With
  On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Here is another non-looping macro that you can also consider...

[table="width: 500"]
[tr]
	[td]Sub ReplaceMissingData()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(C2:C#="""",G2:G#,C2:C#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[hiker95]

Woofy_McWoof_Woof,

Here is updaated macro for you to consider, that does not do any looping in the active worksheet, thru the rows in column C, and, should be very fast.

Sub ReplaceMissingData_V2()
' hiker95, 08/24/2017, ME1020214
Application.ScreenUpdating = False
Dim lr As Long
With ActiveSheet
  lr = .Columns("A:F").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  On Error Resume Next
  With .Range("C2:C" & lr)
    .SpecialCells(xlCellTypeBlanks).Formula = "=RC[3]"
    .Value = .Value
  End With
  On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Here is another non-looping macro that you can also consider...

[table="width: 500"]
[tr]
	[td]Sub ReplaceMissingData()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(C2:C#="""",G2:G#,C2:C#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

@Woofy_McWoof_Woof,

One note about my code above... when I copy/pasted what you posted into my copy of Excel, Column D remained blank and your last three columns of data went into Columns E, F and G. I notice in hiker95's code that he assumed your columns were contiguous ending at Column F, not G like I assumed. If hike95's layout is correct, then here is my code modified assuming that layout...

[table="width: 500"]
[tr]
	[td]Sub ReplaceMissingData()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C2:C" & LastRow) = Evaluate(Replace("IF(C2:C#="""",F2:F#,C2:C#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

Here is updaated macro for you to consider, that does not do any looping in the active worksheet, thru the rows in column C, and, should be very fast.

@hiker95,

First, let me state that in human terms, the difference in speed between the code you posted in Message #7 and the code I posted in Message #6 would not be noticeable. I am not sure how this will translate to other computer, but on my system, my code was some three times faster than your code BUT, as I said, that difference is not noticeable in human terms. I carried the OP's data layout down through 12000 rows where 4000 of them were blank. On average, my code took 0.02 second to execute whereas your code took 0.06 seconds. Notice how small both of those numbers are which is why I say a human observer would not be able to perceive the difference.

 

[hiker95]

@hiker95,

First, let me state that in human terms, the difference in speed between the code you posted in Message #7 and the code I posted in Message #6 would not be noticeable. I am not sure how this will translate to other computer, but on my system, my code was some three times faster than your code BUT, as I said, that difference is not noticeable in human terms. I carried the OP's data layout down through 12000 rows where 4000 of them were blank. On average, my code took 0.02 second to execute whereas your code took 0.06 seconds. Notice how small both of those numbers are which is why I say a human observer would not be able to perceive the difference.

Rick,

My statements in my reply #5, and, #7, concerning "and, should be very fast.", was in reference to Fluff's reply #2.

 

[Fluff]

My statements in my reply #5, and, #7, concerning "and, should be very fast.", was in reference to Fluff's reply #2.

As my code took under a second to run on approx 14000 rows, I would have to agree with Rick when he said

a human observer would not be able to perceive the difference