Fill in gaps in a column by copying value at beginning of the gap

[gkuwas]

I have a column of data with gaps in it. I want Excel to copy the value at the top of the gap filling the gaps below, eg.

013



015



016
So between 013 and 015 I want to fill in 013 and between 015 and 016 with 015. It is a very long column with loads of gaps. I just wonder if there is a clever way to do this without me manually having to drag the values down in each gap (and with leading zero without increasing the number!!)

Thanks for any help.

 

[SteveO59L]

Re: How to get Excel to Fill in gaps in a column by copying value at beginning of the gap

something along the lines of
Dim c As Range, rng
Set rng = Range("a1:a30")
For Each c In rng
If c.Value = "" Then
c.Value = c.Offset(-1, 0).Value

End If
Next c

 

[TRADER083]

Re: How to get Excel to Fill in gaps in a column by copying value at beginning of the gap

Or try:

Public Sub FillGap()
Dim ws As Worksheet
Dim xlrow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
'Adjust your worksheet name accordingly before running
xlrow = 1
Do Until xlrow = 50000
'Adjust the lenghth of your column here before running
'Also change "A" into your actual column reference
    If ws.Cells(xlrow, "A") = vbNullString Then
        ws.Cells(xlrow, "A") = ws.Cells(xlrow - 1, "A")
    End If
    xlrow = xlrow + 1
Loop
End Sub

 

[Jonmo1]

Re: How to get Excel to Fill in gaps in a column by copying value at beginning of the gap

Without any code...

Highlight the column
Press CTRL + G
Click Special
Select Blanks
Click OK
Press =
Press UP Arrow Key
Press CTRL + ENTER

 

[gkuwas]

Re: How to get Excel to Fill in gaps in a column by copying value at beginning of the gap

Thank you Trader083 - it worked perfectly!

 

[gkuwas]

Re: How to get Excel to Fill in gaps in a column by copying value at beginning of the gap

Thank you!!

Or try:

Public Sub FillGap()
Dim ws As Worksheet
Dim xlrow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
'Adjust your worksheet name accordingly before running
xlrow = 1
Do Until xlrow = 50000
'Adjust the lenghth of your column here before running
'Also change "A" into your actual column reference
    If ws.Cells(xlrow, "A") = vbNullString Then
        ws.Cells(xlrow, "A") = ws.Cells(xlrow - 1, "A")
    End If
    xlrow = xlrow + 1
Loop
End Sub

 

[TRADER083]

Re: How to get Excel to Fill in gaps in a column by copying value at beginning of the gap

No problem Just be mindful that if the first row is an empty cell the code will break. So perhaps change xlrow from = 1 to = 2 in the future to prevent extreme scenarios.

 

[gkuwas]

Re: How to get Excel to Fill in gaps in a column by copying value at beginning of the gap

No probs, thanks!

No problem Just be mindful that if the first row is an empty cell the code will break. So perhaps change xlrow from = 1 to = 2 in the future to prevent extreme scenarios.