Delete cells in an Excel column when rows = 0

excel, vba

Solution

Deleting cells in a loop can really be very slow. What you could do is identify the cells that you want to delete in a loop and then delete them in one go after the loop. Try this.

Option Explicit

Sub Sample()
    Dim row_index As Long, lRow As Long, i As Long
    Dim ws As Worksheet
    Dim delRange As Range

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    row_index = 7

    Application.ScreenUpdating = False

    With ws
        lRow = .Range("P" & .Rows.Count).End(xlUp).Row

        For i = row_index To lRow
            If .Range("P" & i).Value <> "" And .Range("P" & i).Value = 0 Then
                If delRange Is Nothing Then
                    Set delRange = .Range("P" & i)
                Else
                    Set delRange = Union(delRange, .Range("P" & i))
                End If
            End If
        Next
    End With

    If Not delRange Is Nothing Then delRange.Delete shift:=xlUp
    Application.ScreenUpdating = True
End Sub

Problem

I am trying to delete all cells =0 in a column in my spreadsheet and "summon" the values which don't to the top of the column. I am currently using ``` Dim row_index As Integer Dim col_index As Integer row_index = 7 col_index = 16 Application.ScreenUpdating = False 'turns off screen updates While Cells(row_index, col_index) <> "" If Cells(row_index, col_index) = 0 Then Cells(row_index, col_index).Delete Else row_index = row_index + 1 End If Wend Application.ScreenUpdating = True 'turns screen updates back on ``` But even with screen updating off it is very slow as the datasets are between 500-3500 points. Is there a better way to do this or any other tips to speed it up? Thanks Edit: there are a few solutions on the web but they all seem to involve blanking cells or deleting rows. I only want to delete cells and then shift cells up.

Original source