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
357
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Set Rng = Range("D3:D", Range("F" & Rows.Count).End(xlUp).Row)
and
VBA Code:
Set RngBlanks = Rng.SpecialCells(xlBlanks)(1)
 

danbates

Active Member
Joined
Oct 8, 2017
Messages
357
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Oops, it should be
VBA Code:
Set Rng = Range("D3:D" & Range("F" & Rows.Count).End(xlUp).Row)
 

danbates

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

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
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.
 

danbates

Active Member
Joined
Oct 8, 2017
Messages
357
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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
Top