I have a spreadsheet with product names in column A which alternate product name, blank row, product name, blank row etc. However the starting row in column A as well as how many products and therefore blank rows is variable.
I'm trying to write some code so that I can reformat the data to not have any blank rows in it, but am getting stuck on a way to delete the appropriate number of blank rows. Here is what I have so far:
Dim i as long
Dim j as long
i = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
Do Until i = 1
Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Delete
i = i - 1
Loop
The i index gets the number of non-blank rows in column A. My plan was to then loop i-1 times to delete the blank rows.
This loop doesn't quite work though since when a row is Deleted and I End(xlDown) I go to the top of the items in the row rather than the bottom.
Any help appreciated.
