CODEWITHSUNDEEP
excelvbadelete-row
Papa
Papa

Reputation: 17

Cannot delete rows in Excel on the sheet or with VBA

I am creating a new worksheet and copying data from three others into it. Two of the worksheets have empty rows in them that get copied to the new sheet. After the copies, I sort the data to get the empty rows to collate to the bottom. I then find the last row containing data, add one to it, and attempt to delete the remaining rows. If I go to the sheet and highlight the rows, it doesn't matter if I use Clear Contents or Delete, the rows don't go away (CTRL+END verifies this). I have to copy the entire range of rows from each sheet because data is placed in random rows and I never know which rows will be blank (this is for a raffle application).

Using VBA, I've tried:

Dim lastrow as long
lastrow = Sheets(strSheetName).Cells.Find("*", SearchOrder:=xlByRows, 
SearchDirection:=xlPrevious).Row
lastrow = lastrow + 1
Rows("lastrow:lastrow").Select
Range(Selection, Selection.End(xlDown)).Select

Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

I've tried xlCellTypeAllFormatConditions, xlCellTypeBlanks, and xlCellTypeSameFormatConditions. I realize there is some type of formatting in those "empty" rows but I just can't get them to delete. How do I get rid of those rows that appear blank but apparently are not?

Upvotes: 0

Views: 64

Answers (0)

Related Questions