SpecialCells Contants

[PippaThePointer]

Hi,
I have a line in my VBA that very neatly removes all rows that have blank in row 'QTY'
Columns("D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

I would like to do the same if the the cell D is not a number. According to Microsoft page i can do it with the following, but i can work out how to write it. Ive tried everything.

"Optional Object. If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type. Can be one of the following XlSpecialCellsValue constants:xlErrorsxlLogicalxlNumbersxlTextValues"
I thought i would just need "Columns("D").SpecialCells(xlCellTypeConstants:xlTextValues).EntireRow.Delete"

Can anyone help me out?

 

[PippaThePointer]

It already stops at row 2

For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1

When you delete rows using looping Excel shifts the rows up, if you started from the top going down this would make it miss rows and so you work from the bottom going up then the shift has no effect on the results

thanks. I had left a line of entireRow.Delete in there after your new code.

 

[nemmi69]

Thank you. That was so simple.
Do you know if its possible to use this simple code to combine 2 conditions or do i have to then write more codes?
For example If column A contained text and Column D contained numbers, delete row?

Im learning bits and pieces and using this line of code in a macro that has consolidated a whole bunch of worksheets and would then get rid of all the empty qty rows. After i consolidate to one sheet I am removing blank rows, rows with no qty in D and rows also trying to get rid of any 'totals' in the source worksheets.
Eventually i want to take these basic consolidated columns and copy them into another master worksheet tempate that has other forumulas and maro - But i havnt worked out how im going to do that yet.

Glad to help