Delete rows of records till null row

Microsoft Excel 2003 (full product)
February 25, 2010 at 19:30:11
Specs: Windows XP
Hi,

I am trying to figure out how to delete rows of old records through VBA. I created a macro that deleted only one record at a time which isn't feasible if the report contains over 100 rows of data..

I want to create a command button that will actively seek out cells in column 'A', starting at "A5", that is not null and delete the entire row. I am hoping the macro will stop when it detects the first cell (row) in column 'A' that does not contain any record.


See More: Delete rows of records till null row

Report •


#1
February 25, 2010 at 20:24:17
This should do it for you.

I highly suggest that you try it in a backup copy of your workbook in case something goes terribly wrong.

As written, it will delete Rows 5 through the row above the empty cell in Column A. If you want to delete the blank row also, just add 1 to endRange.

Option Explicit
Sub DelRowTillNull()
Dim lastRow, nextRow, endRange
'Find last used cell in Column A
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
'Loop through column A looking for an empty cell
  For nextRow = 5 To lastRow
'Stop checking when empty cell is found
   If Cells(nextRow, 1) = "" Then Exit For
'Increment counter each time a non-empty cell is found
    endRange = nextRow
  Next
'Don't delete anything if an empty cell isn't found
   If endRange = lastRow Then Exit Sub
'Delete rows 5 through the blank row minus 1
   Rows("5:" & endRange).EntireRow.Delete shift:=xlUp
End Sub


Report •

#2
March 1, 2010 at 06:33:39
Thank you for the code. The code works to a certain extent.

I created a mock spreadsheet with 21 rows of old records. Starting from Column 'A', Row '5', the command deleted 18 rows of records, leaving the last three rows of records untouched.

I changed the number of rows with old records (18, 32, and 6) and in each instance; the last three rows of records were not deleted. Is there something I can do to ensure that the last three rows are counted and deleted?


Report •

#3
March 1, 2010 at 08:37:19
I can't see your data from here, so it's a little hard to determine what's going on.

As per your OP "I am hoping the macro will stop when it detects the first cell (row) in column 'A' that does not contain any record."

If you have an empty cell in Column A anywhere in your data, the code will only delete data above that empty cell.

In other words, If you have data in A5:A16, an empty cell in A17 and data in A18:21, the code will delete A5:A16 because it stops when it gets to A17, which is empty.

Is that not what's happening and/or is that not what you want?


Report •

Related Solutions

#4
March 1, 2010 at 08:59:08
I understand that if I have an empty cell in Columna A in my data, the code will only delete above that empty cell. That is exactly what I want.

The issue is, there aren't, and will not be, any empty cell in Column A. Column A in my spreadsheet is a required field.

What happening here is: I have data in A5:A:25 with no empty cells. The code deleted A5:A22. But A23:A25 still has value (not empty) and yet, was not deleted.

I tried different number of rows (A5:A10 and A5:A50) with no empty cells in Column A. The last three rows in each tests were not deleted.

I decided to try make only three rows (A5:A7) and ran the code. It did not even delete the rows.


Report •


Ask Question