Delete cells in an Excel column when rows = 0

by

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

More Related Contents:

  1. Print function selecting wrong Type of Change option excel vba
  2. Format Cell Contents
  3. Excel formula checklist in employees
  4. How do I put double quotes in a string in vba?
  5. What is the difference between .text, .value, and .value2?
  6. Excel VBA – How to add dynamic array formula
  7. How can I URL encode a string in Excel VBA?
  8. VBA rounding problem
  9. VBA – Write all possible combinations of 4 columns of data [duplicate]
  10. Remove duplicates from array using VBA
  11. 2 ways for “ClearContents” on VBA Excel, but 1 work fine. Why? [duplicate]
  12. Excel 2013 VBA Clear All Filters macro
  13. How to automatically insert a blank row after a group of data
  14. Get File Path (ends with folder)
  15. “Can’t find Project or Library” for standard VBA functions
  16. How do I issue an HTTP GET from Excel VBA for Mac
  17. Update excel sheet based on outlook mail [closed]
  18. Copy sheet and get resulting sheet object?
  19. Sending mail using Outlook where the Send method fails
  20. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  21. Export chart as image – with click of a button
  22. Error13 in Excel VBA in data file
  23. How to get the path of current worksheet in VBA?
  24. Excel VBA to match and line up rows
  25. Change font color for a part of text in cell
  26. Create a new object using the text name of the class
  27. Excel cell from which a Function is called [duplicate]
  28. How to extract text within a string of text
  29. GET pictures from a url and then rename the picture
  30. How to protect Excel workbook using VBA?

Leave a Comment