copy from cell above

[Annette]

I have over 4,000 rows ... wherein there are blank rows that need text
copied from one cell above the blank cell and paste to the blank cell.

example:

drii
drii
(blank) < copy drii from above and paste here
drip
drip
drip
(blank) < copy drip from above and paste here

Where there are blanks, I want the macro to copy the cell above and paste
.... all the way down ...

I hope someone has something already written. THanks for your help.

 

[Kalle]

for i = 1 to Maximum
if Worksheets("Yoursheet").cells(i+1, DesiredColumn).value = "" then
Worksheets("Yoursheet").cells(i+1, DesiredColumn).value =
Worksheets("Yoursheet").cells( i , DesiredColumn).value
endif
next

 

[Bernie Deitrick]

Annette,

Select your column, the use Edit | Go To.... select Special, then Blank
cells. click OK. Then type = and hit the up arrow, and use Ctrl-Enter.
Then copy the entire column and pastespecial values.

HTH,
Bernie
MS Excel MVP

 

[Chip Pearson]

Annette,

Try the following code:

Dim LastRow As Long
Dim RowNdx As Long

With ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = 1 To LastRow
If .Cells(RowNdx, "A").Value = "" Then
.Cells(RowNdx - 1, "A").Copy
Destination:=.Cells(RowNdx, "A")
End If
Next RowNdx
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Frank Kabel]

Hi Annette
one way try the following (for column A):

sub fill_entries()
Dim row_index as long
dim lastrow as long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For row_index = 1 to lastrow
if cells(row_index,"A")=""
then cells(row_index,"A").value = _
cells(row_index-1,"A").value
end if
next
end sub


Another way:
- select this range
- hit F5
- choose 'special'
- select 'Empty cells' (could also read 'blank cells')
- hit OK

if the first blank cell is A9 enter the following formula
=A8
now hit CTRL+ENTER (not only a single 'ENTER')

 

[Annette]

WHEW .. you are very fast and in the nick of time ... THANKS ... I was
getting tired of copying and pasting! Learned something new today! Thanks
Kalle, Bernie, Chip and Frank!

 

[Tom Ogilvy]

If you have constant values in column 1, then this would be significantly
faster:

Sub Tester6()
Dim rng2 As Range, rng As Range
Set rng2 = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
On Error Resume Next
Set rng = rng2.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.FormulaR1C1 = "=R[-1]C"
rng2.Formula = rng2.Value
Else
MsgBox "Non found"
End If
End Sub

It is Bernie's method in Code.

If it is a one time deal, then you are already done. But for those who
prefer a faster approach (although as written, the column should contain
constants as formulas would be converted).