Autofill column blank cells: Fill the blank cell with the closest cell with a value above it.

kevinlarey

New Member
Joined
Mar 5, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi!

I'm trying to figure out if there's a VBA or formula that I can autofill the blank cells in column B by taking the closest cell with a value above the blank cell and paste it.
When there is only one cell to fill, it's easy to use a formula but when there is multiple cells such as B13-B17, I can't find a way to paste TNT-4004 in all of those cells.

I don't want to manually double click the bottom right corner for each cell (TNT-4001, TNT-4002, TNT-4003, TNT-4004) since I have to do this for 100,000+ lines.

I'm really hoping there is a solution for this!

Thank you!
 

Attachments

  • 2.PNG
    2.PNG
    10 KB · Views: 5

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Noxqss38242

Board Regular
Joined
Sep 15, 2017
Messages
138
Office Version
  1. 2016
If cell is truly blank (which if cell has a space in it, it won't pick up a true blank)
On the home tab, on the far right should have "Find & Select"
"Go to Special"
Select "Blanks"
Click ok
All the blanks are now highlighted on the sheet
Go to the formula bar
Enter =
Then click on the cell above the blank with data in it
Then hold down Control button and hit enter.
 
Solution

kevinlarey

New Member
Joined
Mar 5, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
If cell is truly blank (which if cell has a space in it, it won't pick up a true blank)
On the home tab, on the far right should have "Find & Select"
"Go to Special"
Select "Blanks"
Click ok
All the blanks are now highlighted on the sheet
Go to the formula bar
Enter =
Then click on the cell above the blank with data in it
Then hold down Control button and hit enter.
Hi Noxqss38242:

Wow! Thank you so much!

Such a simple solution but I was able to save a couple hours!

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,400
Messages
5,641,927
Members
417,247
Latest member
Chitaah

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