Excel - Automatically Filling Empty Cell Closed

Question

Hello, 

I have an Excel worksheet that has two columns: CSJNumber (column A) and ARRARvw (column B).  Cell A2 and every other cell in that column has a value in it.  Cell A3 and every other cell in that column is empty.  I need to duplicate cell A2 into the cell just below it (the empty cell), and do the same thing for cell A4, A6, etc., and repeat this process throughout the 800 plus rows in the worksheet without affecting any values in column B.  Can anyone recommend a way to do this without doing the manual copy and paste?  Thanks in advance for any help.Configuration: Windows XP / Internet Explorer 7.0

venkat1926 · Accepted Answer

[quote]
Cell A2 and every other cell in that column has a value in it. Cell A3 and every other cell in that column is empty.
[/quote]

A2 and A3 are in the same column 
do you mean that A2 and every other cell in that ROW has a value etc. 

I presume similar row 4 has values and row 5 empty 

you want duplicate row 2 into row3 and similarly row 4 to row 5.  please clarify 
guve a small example of data and the result you require.

ophirw · Answer

I have the same problem,
I have a very large DB table that I want to fill in empty blank cells.
I have a column of names that are not all full, I need the macro to automatically copy a name to all the empty cells bellow it until it reaches the next cell that has a name & than copying it to all the empty cells bellow it.  the macro should run until the end of the table.

I have a few tables like this so I need the macro to start from the cell I chose (the 1st cell has data) & go down until the end of the table, each table has a different number of rows & usually the last row in that column is empty.

venkat1926 · Answer

ophirwq

can you please post a small extract of your database(you can park it in speedyshare.com without password) and explain clearly what you want with examples from the database.

ophirw · Answer

thanks for the fast reply.

I just got it going, I was fulling around with this for 2 days.
what I needed was to fill in the blanks in empty cells of a table, for example:
A            B
a            1
              1
              1
b            1
              1
              1

but not long after I posted this (after being very frustrated) I found on the net the solution.

1st I copied this function from the net (very usfull):

' this function will return the number of the last row that was used in the sheet
' function from: http://excelvbamacro.com/number-of-cellsrowscolumns-with-formula/
Function LastRow() As Long
    Dim ix As Long
    ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    LastRow = ix
End Function

& I used it in the macro that fills out the blanks:

Sub Auto_fill_cells()
' Auto_fill_cells Macro
' the macro will run on the active cell column starting from the selected cell & will fill in all the blank
' cells with the 1st data above them until the end of the last row that was used in the sheet.
Dim rngend As Integer
Dim varCellValue As String
Dim n As Double

'find end of data in the sheet
rngend = LastRow()

'loop through the used cells
For n = ActiveCell.Row To rngend
    If Application.IsText(ActiveCell) = True Then
        varCellValue = ActiveCell.Value
    Else
        If ActiveCell = "" Then
            ActiveCell.Value = varCellValue
        Else
            MsgBox "ERROR"
            Exit Sub
        End If
    End If
    ActiveCell.Offset(1, 0).Select
Next n

End Sub

& the result is:
A            B
a            1
a            1
a            1
b            1
b            1
b            1


I have a few files with about 7K rows cant do this any other way.

thanks

ophirw · Answer

Doudble L,

I hope I got your question right, this is not a formula. it is a VBA code.
VBA stands for Visual Basic for Applications which is a simple programing language for office applications (by Microsoft).
1st you need to open the VBA editor through the excel.

If you know nothing about this I would recommend you search for "excel VBA tutorial' on Google.   it is very simple & you can lean very fast.

if you know a bit about the editor all you need to do is copy the code I posted into a module & run it

I will post the most updated macro I have so just copy it all into a module & it should work:

The macro uses this following sub & function:

1. This Sub is in order to play a sound when the macro finishes, it is very useful when it takes a long time for the macro to run (you can change the sound file to your taste:

Public Sub playfinishsound()
sndPlaySound32 "C:\WINDOWS\Media\Windows XP Shutdown.wav", 0&
End Sub

2. this function will return the number of the last row that was used in the sheet

Function LastRow() As Long
    Dim ix As Long
    ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    LastRow = ix
End Function

This is the macro itself:

Sub Auto_fill_cells()

' Auto_fill_cells Macro
' the macro will run on the active cell column starting from the selected cell & _will fill in all the blanks
' cells with the 1st data above them until the end of the last row that was used in the sheet.

Dim rngend As Integer
Dim varCellValue As String
Dim n As Double

'find end of data in the sheet
rngend = LastRow()

'loop through the used cells
For n = ActiveCell.Row To rngend
    If ActiveCell.Value <> "" Then
        varCellValue = ActiveCell.Value
    Else
        If ActiveCell = "" Then
            ActiveCell.Value = varCellValue
        Else
            MsgBox "ERROR"
            Exit Sub
        End If
    End If
    ActiveCell.Offset(1, 0).Select
Next n

playfinishsound
End Sub

this code uses excel 2007!

Excel - Automatically Filling Empty Cell

5 responses

Similar discussions

Subscribe To Our Newsletter!