Excel VBA Delete Rows

by

This is a common problem when deleting rows. Imagine you are moving through your for loop one row at a time and deleting:

For cnt = 7 To dlt
    Rows(cnt).EntireRow.Delete
    cnt = cnt + 1
Next

You are on row 7 and you delete it. This shifts all of your rows up. Row 8 is now Row 7. You then increase your cnt variable by 1 (to 8) and delete row 8. But you missed row 7, which was row 8… it’s crazy bananas.

Instead, change your for loop to work backwards:

For cnt = dlt to 7 step -1
    Rows(cnt).EntireRow.Delete
    cnt = cnt - 1
Next

This way the row shifting doesn’t affect your cnt.

More Related Contents:

  1. How do I get values from a hidden worksheet using VBA [closed]
  2. How to copy cells that contain certain text to another worksheet [closed]
  3. VBA Macro for copying and pasting columns on same excel sheet
  4. Excel VBA, code to count the # of filled cells, then find highest and 2nd highest and subtract the corresponding column #’s [closed]
  5. Levenshtein Distance in VBA [closed]
  6. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  7. How do I base64 encode a string efficiently using Excel VBA?
  8. Assign event handlers to controls on user form created dynamically in VBA
  9. Programmatic Access To Visual Basic Project Is Not Trusted
  10. Can I make this macro more efficient or faster?
  11. Conditional Formatting using Excel VBA code
  12. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  13. How to get the background color from a Conditional Formatting in Excel using VBA
  14. VBA Excel Populate ListBox with multiple columns
  15. VBA Integer Overflow at 70,000
  16. How to count up text of a different font colour in excel
  17. How to Lock the data in a cell in excel using vba
  18. How to use Events with Option Button Controls on Userform [duplicate]
  19. How do I use variables to set properties in VBA (Excel)
  20. VBA: Else without If Error
  21. How to highlight a cell using the hex color value within the cell?
  22. Convert RTF (Rich Text Format) code into plain text in Excel
  23. lastrow and excel table.
  24. Open a workbook from VBA and disable Workbook_Open() code?
  25. Creating form programmatically in the module using vba
  26. How to filter listbox values based on a Textbox value
  27. Find and FindNext for Excel VBA
  28. How to have vba execute every 10 minutes?
  29. Read and write from/to registry in VBA
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment