PDA

View Full Version : Solved: Empty Cells Will Not Delete



Mavis Beacon
03-05-2013, 06:51 AM
Hello,
My data set has a number of columns, all of which have a different number of cells.
I'm trying to clean my data by removing blank cells before I run macro as when I use the .End(xlDown) function it runs for cells that are blank.
I've tried using the specialcells(xlcelltypeblanks).delete and IsEmpty but these only seem to work for entire rows that are blank. I also tried using this code:

Dim row As Variant
Dim i
row = Range("A3:BZ234")
For Each i In Range("A3:BZ234")
i.Select
If ActiveCell.Value = 0 Then
ActiveCell.Delete Shift:=xlShiftUp
End If
Next i

but it still leaves blank cells (of different amounts!?) under each column. I'm at a loss. Any help/advice would be greatly appreciated.

SamT
03-05-2013, 08:51 AM
Hello Mavis Beacon,

Welcome to VBAX, the best VBA site on the Web, and thank you for teaching me how to type.

"Row" is a keyword in VBA so when you say
row = Range("A3:BZ234") Row returns the first Row in the Range (Row 3)
Best if you set Option Explicit at the top of your Code Page, or use VBA Menu >> Tools>> Options >> Check mark in "Require Variable Declaration"

Even if it works
For Each i In Range("A3:BZ234") travels across each row, then drops down to the next row and travels across it. So when you delete a cell, the blank under it moves up into the row just traversed and is not checked again.

If you travel down each column, one at a time, then you must recheck each location (Cell) after deleting to see if it is still blank.

Traveling up each column will prevent this situation.

Mavis Beacon
03-05-2013, 10:48 AM
Thanks SamT! I managed to work around it by putting
Do
ActiveCell.FormulaR1C1 = "=RC[-1]*100"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset(0, -1) = vbNullString

into the other macro I was using (copied and pasted directly from someone elses code of course). Cheers,
M.