Need faster code to delete blank cells

[mfarr76]

I have a column that is a result of A copy/paste code of values only, no formulas. The code below is design to remove cells that are blank (""), it works but is slow as it goes through its process. Any suggestions on how to speed this up?

'COPYPASTE AS VALUES          
Application.ScreenUpdating = False
Sheets("Main").Select
Range("B20:B5000").Copy
Range("C20").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


'REMOVE BLANK SPACES
For i = 20 To 300
    If Cells(i, 3).Value = "" Then
    Cells(i, 3).Delete shift:=xlUp
    End If
Next i

Thanks in advance!

 

[njimack]

Sub x()

'COPYPASTE AS VALUES
Application.ScreenUpdating = False
With Sheets("Main")
    Range("B20:B5000").Copy
    Range("C20").PasteSpecial xlValues


    Range(.Cells(20, 3), .Cells(300, 3)).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End With


Application.ScreenUpdating = True


End Sub

 

[mfarr76]

Thanks for the reply!

I copy/paste the code into my workbook and it copied the values over but it did not delete/shift up the cells with blank spaces, any suggest on what went wrong?

 

[MARK858]

Not that it should male a difference (if the copy/paste did work) but the code is missing some periods/full stops...

Sub x()

'COPYPASTE AS VALUES
Application.ScreenUpdating = False
With Sheets("Main")
   [COLOR="#FF0000"] .[/COLOR]Range("B20:B5000").Copy
   [COLOR="#FF0000"] .[/COLOR]Range("C20").PasteSpecial xlValues


   [COLOR="#FF0000"] .[/COLOR]Range(.Cells(20, 3), .Cells(300, 3)).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End With


Application.ScreenUpdating = True


End Sub

If the code doesn't delete

So the big question is are your cells actually blank?
Put

=ISBLANK(C20)

in an empty column and drag down. Do the cells that are "blank" come up as false or true?

 

[Jonmo1]

The problem with using SpecialCells(xlCellTypeBlanks) is that formulas returning "" are NOT blank.
Even after the copy/paste special/values

You have to add in a .Value = .Value line

Try

Sub x()

'COPYPASTE AS VALUES
Application.ScreenUpdating = False
With Sheets("Main")
    .Range("B20:B5000").Copy
    .Range("C20").PasteSpecial xlValues


    With .Range("C20:C300")
        .Value = .Value
        .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
    End With
End With


Application.ScreenUpdating = True


End Sub

 

[Jonmo1]

Now, is there a particular reason you copy/pasted range B20:B5000
But only processing C20:C300 for blanks?

 

[mfarr76]

I initially limited it to process that number of cells because it took so long with the code I was using.

The suggestion Jonmo1 made was the secrete, thank you for the response! It works great now.

 

[Jonmo1]

You're welcome.