CODEWITHSUNDEEP
excelvbaautofill
user3399352
user3399352

Reputation: 15

VBA replace value with cell above until blank and loop

I'm looking to fill values down based on the cell above until blank cell, this should continue until there is no more content, for example: refer the below table, copy the top value down until blank, therefore, 'petty msa' will copy down to 'inter' and stops, 'petty ksm' will copy down until 'general', 'comp' will copy down until 'motor'.

Column: A
petty msa
inter

petty ksm
welfare
water
prepay
general

comp
travel
motor

Edit: This is a long list of approx 15,000 rows

Thanks

Upvotes: 0

Views: 1637

Answers (3)

The King
The King

Reputation: 4650

Insert a blank row in A1. In cell B2 paste the below formula and copy it down.

= if(A1="",A2,B1)

This should work

Upvotes: 0

user3598756
user3598756

Reputation: 29421

given your data "structure" as per your example, this should do:

Option Explicit

Sub main()
    Dim area As Range

    With Worksheets("filldown").Columns("A").SpecialCells(xlCellTypeConstants) '<--| change "filldown" to your actual worksheet name and "A" to actual index of column with data to be "filled down"
        For Each area In .Areas
            area.Value = area(1)
        Next area
    End With
End Sub

Upvotes: 0

DOGA
DOGA

Reputation: 21

Select the first cell where you have value 'petty msa' then- 1. press ctrl+shift+down -to select the range to be copied 2. press ctrd+down - to copy down the values Now you have copied 'petty msa' upto the cell containing 'inter'

repeat this process for each range.

Upvotes: 2

Related Questions