0

I have an excel worksheet where I have sorted into date order and this leaves an awful lot of blanks at the bottom of the spreadsheet which I would like to delete but leave the final two. (the one at the very bottom is formatted blue to the height of 6 and the one just above is blank and also to the height of 6).

The data is stored from A5 to J and varies in length - tab name is Date Order

Does anyone know of any VBA code that will help with this problem.

Any help would be greatly appreciated.

Community
  • 1
  • 1
TkdKidSnake
  • 69
  • 1
  • 7
  • Have you considered looping and checking the cell's `interior.color` and `cell.Height`? – Siddharth Rout Feb 12 '14 at 20:49
  • I did think that this may be away but i'm not sure how to do it - can you help? – TkdKidSnake Feb 12 '14 at 21:08
  • This is one scenario when I would normally suggest a user to loop through the `Usedrange`. Use something like this `For i = LastRow To ThisWorkbook.Sheets("sheet1").UsedRange.Rows.Count` and then check for `cell.interior.color` and `cell.Height`. To find the last row and to understand how `usedrange` works, see [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) link. – Siddharth Rout Feb 12 '14 at 21:13
  • Would it be easier if it deleted the blank rows between the data before the sort? rather than trying to find them after – TkdKidSnake Feb 12 '14 at 21:17
  • Yes you can do that as well. You may want to see [THIS](http://stackoverflow.com/questions/19195515/trying-to-delete-a-row-if-no-data-in-row-aj) – Siddharth Rout Feb 12 '14 at 21:25

1 Answers1

1

This does the trick if anyone wants the code however it does not leave the last two rows does anyone know how to achieve this?

' This macro deletes all rows on the active worksheet
' that have no value in column A.
Dim iRow As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
For iRow = LastRow To 1 Step -1
If Cells(iRow, 1) = "" Then Rows(iRow).Delete
Next iRow
TkdKidSnake
  • 69
  • 1
  • 7