Delete a row in Excel VBA

by

Chris Nielsen’s solution is simple and will work well. A slightly shorter option would be…

ws.Rows(Rand).Delete

…note there is no need to specify a Shift when deleting a row as, by definition, it’s not possible to shift left

Incidentally, my preferred method for deleting rows is to use…

ws.Rows(Rand) = ""

…in the initial loop. I then use a Sort function to push these rows to the bottom of the data. The main reason for this is because deleting single rows can be a very slow procedure (if you are deleting >100). It also ensures nothing gets missed as per Robert Ilbrink’s comment

You can learn the code for sorting by recording a macro and reducing the code as demonstrated in this expert Excel video. I have a suspicion that the neatest method (Range(“A1:Z10”).Sort Key1:=Range(“A1”), Order1:=xlSortAscending/Descending, Header:=xlYes/No) can only be discovered on pre-2007 versions of Excel…but you can always reduce the 2007/2010 equivalent code

Couple more points…if your list is not already sorted by a column and you wish to retain the order, you can stick the row number ‘Rand’ in a spare column to the right of each row as you loop through. You would then sort by that comment and eliminate it

If your data rows contain formatting, you may wish to find the end of the new data range and delete the rows that you cleared earlier. That’s to keep the file size down. Note that a single large delete at the end of the procedure will not impair your code’s performance in the same way that deleting single rows does

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. How to avoid using Select in Excel VBA
  3. How do I use FileSystemObject in VBA?
  4. Is the . in .Range necessary when defined by .Cells?
  5. Run Excel Macro from Outside Excel Using VBScript From Command Line
  6. Importing text file into excel sheet
  7. Checking for #N/A in Excel cell from VBA code
  8. Non-web SQL Injection
  9. Error: “Could Not Find Installable ISAM”
  10. Filling any empty cells with the value above
  11. Autofilter with column formatted as date
  12. Check if value exists in column in VBA
  13. hiding formulas in formula bar
  14. How to delete multiple rows without a loop in Excel VBA
  15. VBA Many buttons point to the same _Click sub
  16. Excel VBA Filter Change event handler
  17. How to suppress Update Links warning?
  18. How do we use restful APIs from Excel macros (vba)?
  19. Cannot VBA write data to cells in Excel 2007/2010 within a function
  20. Sorting a dictionary by key in VBA
  21. VBA paste as values – how to
  22. How to send email to multiple recipients with addresses stored in Excel?
  23. What are the benefits of using Classes in VBA? [closed]
  24. Writing a VLOOKUP function in vba
  25. Handle cancellation of InputBox to select range
  26. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  27. VBA: How to combine two ranges on different sheets into one, to loop through
  28. Function Overloading and UDF in Excel VBA
  29. VBA: Modify chart data range
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment