Populating Blank Cells

dmill8023

Board Regular
Joined
Aug 24, 2012
Messages
60
Hello All--My issue is I have about 26,000 rows of data. Of these 26K rows I have unique identifiers in Column A. Row 1 Column A has Unique identifier 3000600850 then has no value in the next 4 rows (entered blank for example). After those 4 rows is 3000600944 followed immediately by 3000601323 and 3 more blank rows. This happens throughout the rest of this worksheet until the 26000 rows.
Those blank rows need to be populated with the value immediately before those rows of blank cells. For instance with 3000600850 the 4 blank rows after need to be populated with 3000600850 until I get to 3000600944. Does anyone know a formula I can add in a column before that would populate those cells--I'd rather not manually go through and copy down.
Thank you all for the help!!
Examples:
Current State
3000594794
3000600850
blank
blank
blank
blank
3000600944
3000601323
blank
blank
blank
3000601327

<tbody>
</tbody>

Desired Outcome
3000594794
3000600850
3000600850
3000600850
3000600850
3000600850
3000600944
3000601323
3000601323
3000601323
3000601323
3000601327
3000601330


<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

iliauk

Board Regular
Joined
Jun 3, 2014
Messages
163
I think this:

Code:
Dim lastrow As Long
lastrow = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Row

With Range("A1:A" & lastrow).SpecialCells(xlCellTypeBlanks)
   .FormulaR1C1 = "=R[-1]C"
End With
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
532
Office Version
  1. 365
Platform
  1. Windows
Here is a similar version
Code:
Sub slowboat()
    Dim r As Long, r1 As Long
    
    r = Cells(Rows.Count, 1).End(xlUp).Row
    For r1 = 2 To r
        If Cells(r1, 1) = "" Then
            Cells(r1, 1) = Cells(r1 - 1, 1)
        End If
    Next r1
End Sub

This will not handle the last entry.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
Or highlight cells, Press CNTL-G, Press Special, Tick Blanks and OK.

You will be in the first blank. Press =, then UP arrow, then CNTL-ENTER.

Paste them as values if required.
 

dmill8023

Board Regular
Joined
Aug 24, 2012
Messages
60
Thank you everyone who has replied! I am not well versed in writing macros however the CNTL-G-->Special-->Blanks + OK worked wonderfully! Thank you Steve The Fish for that response. Very simplistic way to solve my issue.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,964
Messages
5,621,858
Members
415,862
Latest member
nascaline

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