delete empty cell and zero value cell and shfit up

**abdi1** · 09-19-2010, 07:24 AM

hi
i have a data in cloumn E , within the data i have empty cells and zero values
could any one help in a macro that delete the empty and zero values cells and shift the cells up and copy the result to column i
thanks

Please Login or Register  to view this content.

**pike** · 09-19-2010, 07:36 AM

Hi abdi1
welcome to the forum
something like...

Please Login or Register  to view this content.

**abdi1** · 09-19-2010, 08:16 AM

hi
thanks for ur help
iv tried ur code but it seem that somthing is wrong

below is an attached copy of excel sheet including an example of my numbers
could u plz tray the code again on my excel sheet
thanks

**pike** · 09-19-2010, 11:39 PM

hey abdi1,
Should have tested it first but This will..

Please Login or Register  to view this content.

**JieJenn** · 09-23-2010, 10:07 AM

Just copy and paste the code to your excel workbook

Option Explicit
Dim LR As Long

Sub test()

Dim i As Long

LR = Cells(Rows.Count, 5).End(xlUp).Row

For i = LR To 1 Step -1
If IsEmpty(Cells(i, 5)) = True Then
Cells(i, 5).EntireRow.Delete shift:=xlUp
End If
Next i

LR = Cells(Rows.Count, 5).End(xlUp).Row
Range("E1:E" & LR).Copy Range("i1")

End Sub

**pike** · 09-23-2010, 06:08 PM

Hi JieJenn
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here