Fill empty cells upwards with the last non empty cell in a column but stop at the next non empty cell.

danbates

Active Member
Joined
Oct 8, 2017
Messages
377
Office Version
  1. 2016
Platform
  1. Windows
Hi,

What I would like to do is alter this code so it only fills the empty cells from the last non empty cell in col D up to the next non empty cell and then stops there.

VBA Code:
Sub FillBlanks()
  Dim Rng As Range, RngBlanks As Range
  
  Set Rng = Range("D3", Range("F" & Rows.Count).End(xlUp))
  On Error Resume Next
  Set RngBlanks = Rng.SpecialCells(xlBlanks)
  On Error GoTo 0
  If Not RngBlanks Is Nothing Then
    RngBlanks.FormulaR1C1 = "=R[1]C"
    Rng.Value = Rng.Value
  End If
End Sub

I hope this makes sense and any help would be appreciated.

Thanks

Dan
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try
VBA Code:
Set Rng = Range("D3:D", Range("F" & Rows.Count).End(xlUp).Row)
and
VBA Code:
Set RngBlanks = Rng.SpecialCells(xlBlanks)(1)
 
Upvote 0
Hi Fluff,

Unfortunately it hasn't worked.

I am getting the following error:

run time error 1004 - method range of object global failed

and it highlights this line

VBA Code:
Set Rng = Range("D3:D", Range("F" & Rows.Count).End(xlUp).Row)

thanks
Dan
 
Upvote 0
Oops, it should be
VBA Code:
Set Rng = Range("D3:D" & Range("F" & Rows.Count).End(xlUp).Row)
 
Upvote 0
Hi Fluff,

Your code isn't working the way I would like. I don't think I have explained it very well.

Here is an example of what I would like to happen

Col DCol ECol F
AL01/08/202011:43
1
DB04/08/202005:33
1
1
1
DB08/10/202012:56

If my sheet was like the above data then when I trigger the code the following happens below:

Col DCol ECol F
AL01/08/202011:43
1
DB04/08/202005:33
DB08/10/202012:56
DB08/10/202012:56
DB08/10/202012:56
DB08/10/202012:56

Sorry to mess you about.

Thanks again

Dan
 
Upvote 0
How about
VBA Code:
Sub danbates()
   Dim Ar As Areas
   
   On Error Resume Next
   Set Ar = Range("D3", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Areas
   On Error GoTo 0
   If Ar Is Nothing Then Exit Sub
   With Ar(Ar.Count).Resize(, 3)
      .FormulaR1C1 = "=R[1]C"
      .Value = .Value
   End With
End Sub
Although the data you posted has no blanks in col D. Instead you have a 1 in the "blank" cells with a white font colour.
 
Upvote 0
Hi Fluff,

That's perfect, thank you.

I only put the 1's in col D because without something in there the table bunched up the empty rows when I previewed it.
Then I coloured them white so you couldn't see them, but obviously that didn't work lol

Thanks again

Dan
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top