Automatically Filling Empty Cell
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.
Is this just a one-off exercise ...
... and you are just reluctant to do 800 cut'n'pastes?
If so you would not need to go down the VBA route.
Reserve a column in which you enter the row number, so as to preserve the row order. Fill down can do this with a few mouse clicks.
Copy the filled data area to the clipboard, but excluding the column that records the original row number
Paste it into the blank rows.
Re-sort the worksheet by the column of preserved row numbers (which can then be deleted).
Job done.
Oh, you did say that Every other cell in the column (I assume you mean row) is used, but I suspect that this is unlikely and there is one that you could use for the row number record.
With kind regards
Clint Westwood
Duplicate alternate cell values
Truetide, paste this snippet of code into a module in the Visual Basic Editor:
'Code Starts here:
Sub Traction()
Dim X As Long, BottomRow As Long
BottomRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 2 To BottomRow Step 2
Cells(X + 1, 1).Value = Cells(X, 1).Value
Next X
End Sub
'Code ends
I tried this on a "quick and dirty" set of values and it worked just fine.
Hope this helps,
Bill