Excel macro 1

[bac2u]

Hi, I need a macro to copy the contents of every non-blank cell in a column to the cell below the non-blank cell, then go to the next non-blank cell and repeat the process. There are about 8,000 rows in the column. Thanks in advance for help. I can use the macro recorder but can't get it to repeat down 8,000 rows.

Barb

 

[BiggerBrother]

Use a loop:

Do while i < 8000
if cells(columnNo, i)<>"" then
cells(columnNo,i+1)=cells(columnNo,i)
i=i+1
end if
i=i+1
loop

Change columnNo to the column of your choice. There are two i=i+1 statements as the first moves on to the empty space, and the second moves onto the next cell.

BB

 

[bac2u]

Hi, this is what I recorded, then put your code in:
It doesn't work. I'm trying to copy every non-blank cell in column E to the blank cell below it. I'm missing something, obviously, how to imbed the copy statement around what you gave me. Please help!

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/18/2004 by CouilliardB
'

'
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Do While i < 8000
If Cells(5, i) <> "" Then
Cells(5, i + 1) = Cells(5, i)
i = i + 1
End If
i = i + 1
Loop

End Sub

 

[bac2u]

Hey! It worked! I just had to change your cell statements around to be:
cells(i,columnNo)
cells(i+1, columnNo)= cells(i,columnNo)

Thanks!!!

 

[jozino01]

what is the initial value of 'i'?

 

[bac2u]

it's 5. here it is all together:

Sub Macro1()

i = 5
Do While i < 4610
If Cells(i, 5) <> "" Then
Cells(i + 1, 5) = Cells(i, 5)
i = i + 1
End If
i = i + 1
Loop
End Sub