Auto Populate cells from bottom to top vba

Microsoft Office excel 2007
August 19, 2010 at 03:40:00
Specs: Windows XP
i would like write a vba script to fills the blank cells with the value of the cell directly below it starting from top to bottom with a loop


blank cell 1
blank cell 2
blank cell 3
blank cell 4
blank cell 5
blank cell 6

blank cell 1 to 4 should be fill with 12345
while blank cell 5 to 6 should be fill with 77777

See More: Auto Populate cells from bottom to top vba

Report •

August 19, 2010 at 05:21:44

Here is a macro that will fill the empty cells:

Sub BlankFill()
Dim rngEnd As Range
Dim varCellValue As Variant
Dim n As Double

'find end of data in column A
Set rngEnd = ActiveSheet.Range("A" & CStr(Application.Rows.Count)) _

'loop through the used cells from bottom to top
'note that first cell to be tested will contain a value
For n = rngEnd.Row To 1 Step -1
    'test cell for 'empty'
    If ActiveSheet.Range("A" & CStr(n)) = "" Then
        'put last saved value into this cell
        ActiveSheet.Range("A" & CStr(n)) = varCellValue
        'not empty so get cell value
        varCellValue = ActiveSheet.Range("A" & CStr(n))
    End If
Next n
End Sub

1. The macro works on the Active worksheet.
You may want to use a specific worksheet name, e.g. , Worksheets("Sheet1").Range("A1")
2. The macro is written for data in column A - change the code as required.
3. The macro works back from the last cell containing data in column A to row 1 - edit row 1 if necessary, on this line:

For n = rngEnd.Row To 1 Step -1


Report •

August 19, 2010 at 20:00:43
wow OMG it works like a charm..thank u sooooo much..ok one more thing
actually i have some information beside the first column..
and after using ur macro above, my end result would be this

Column A Column B
12345 something
12345 something
12345 Bank number: 12121212121
12345 Account Number: 123443
12345 something
77777 something
77777 Bank number: 9988777
77777 Account Number: 4546464

how can i further enchance my macro to be like finding 12345 and once i see Bank Number: , i will be able to extract 12121212121 and put it to Column C.
Most probably i need a loop.

Report •

August 20, 2010 at 04:31:07

I need some more information.

The information beside the first column:
Is this a) just in column B or is it in B & C
b) is this information only on the rows that were non-blank to start with

Where does 12121212121 come from.

Am I correct in assuming that you want to place a value in column C, when two values are matched:
A number in column A and specific text in Column B

For this to work, there needs to be a table containing these pairs of data.


Report •

Related Solutions

August 22, 2010 at 23:41:12
thanks i manage to solve it already through so other method =)

Report •

Ask Question