Macro/VBA to Fill blank cells ABOVE a value

Wheelie_Awesome

New Member
Joined
Aug 18, 2021
Messages
4
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
This is a weird one, I didn't know how to title it.
On a certain data set that I export, the data only includes an Account number on a "subtotal" row.
Column A is "Account ######". I first do a text to columns to break those apart. Not an issue already done in VBA.
Now Column A is just "Account" (Which I no longer really need but not the issue I'll delete later) and Column B contains the Account Numbers.

The issue is, these account numbers are only on what are "subtotal" rows of the dataset. For example in Column C, lets say I have Dollars.
So example: A2:B5 are blanks. A6 and B6 will have Account and Number like stated above. C2:C5 will have dollars amounts, and C6 will be the sum of the dollars.
What I'm trying to do is get the account number filled in B2:B5.

This will be repeated down the file, for 10's of thousands of rows. hundreds of accounts.
Each data pull will be different row variations.

Hopefully that explains it well enough. Is there a possible solution to this? Thank you.

Here is an example with colors to represent where the numbers need to go.
1631645219421.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
How about
VBA Code:
Sub Wheelie()
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
   End With
End Sub
 
Upvote 0
Solution
How about
VBA Code:
Sub Wheelie()
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
   End With
End Sub
That worked! Are you able to explain what that's actually doing, just so I can maybe learn a little and not just copy paste. haha. Thank you.
 
Upvote 0
It's putting a formula in the blank cells that looks at the cell below & then converts it into a value.
So the formula for B2 would be =B3
 
Upvote 0

Forum statistics

Threads
1,214,386
Messages
6,119,217
Members
448,876
Latest member
Solitario

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