selecting cells with a certain value and deleting and **** to the left

[EmyrPC]

Hey hey everyone,

I wonder if anyone can help. I have a range (about 11 columns and potentially thousands of rows) which I've copied a formula which could give a result of "" into then copied and pasted as values. Simple enough.

But what I need to do now is delete the "blank" cells and replace the cell with the one to the left. Unfortunately I can't just use xlCellTypeBlanks because Excel it it's wisdom doesn't except "" as a blank cell.

I have been using this code

Range("U6:AS" & Range("A6").End(xlDown).Row).Replace What:="", Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=FalseDim i As Long
Dim j As Long
Range("U6:AE" & Range("A6").End(xlDown).Row).Select
For i = Selection(Selection.Count).Row To Selection.Cells(1, 1).Row Step -1
For j = Selection(Selection.Count).Column To Selection.Cells(1, 1).Column Step -1
If Cells(i, j).Value = 0 Then Cells(i, j).Delete shift:=xlShiftToLeft
Next j
Next i

but as the range is so huge this dramatically increases the time the code takes to run.

Any help is much appreciated.

 

[igold]

I don't really know which cells you actually want to work on, but from using your code you are selecting over 1 million rows. Wouldn't something like this be a little more straightforward.

If this does not work for you perhaps you could better explain what the range of cells you want to deal with is.

    Range("U6:AS" & Range("A6").End(xlDown).Row).Replace What:="", _
    Replacement:="0", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Dim i As Long, j As Long, lRow As Long
    lRow = Cells(Rows.Count, 21).End(xlUp).Row
    For i = lRow To 1 Step -1
        For j = 41 To 21 Step -1
            If Cells(i, j).Value = 0 Then Cells(i, j).Delete shift:=xlShiftToLeft
        Next j
    Next i

 

[EmyrPC]

Brilliant, Thanks igold.

Worked beautifully.

 

[igold]

I am glad that it worked for you. I was happy to help. Thanks for the feedback.