Excel - Automatically Filling Empty Cell

Closed
Report
-
 ophirw -
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.

5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
[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.
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

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.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
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
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!