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?

 

[nemmi69]

I believe this does it

Columns("D").SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete

 

[RoryA]

I thought i would just need "Columns("D").SpecialCells(xlCellTypeConstants:xlTextValues).EntireRow.Delete"

Replace the colon with a comma.

 

[MARK858]

@PippaThePointer
Your code line just needs the : replaced with a ,

Columns("D").SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete

or

Columns("D").SpecialCells(2, 2).EntireRow.Delete

or as per post 2, although I wouldn't mix the syntax

Edit: I see @RoryA beat me to it

 

[PippaThePointer]

I believe this does it

Columns("D").SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete

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.

 

[MARK858]

Do you know if its possible to use this simple code to combine 2 conditions or do i have to then write more codes?

If you were adding the SpecialCells values together then for example for text, logical and errors you would use

Columns("D").SpecialCells(xlCellTypeConstants, xlTextValues + xlLogical + xlErrors).EntireRow.Delete
'or
Columns("D").SpecialCells(2, 22).EntireRow.Delete
' the 22 is 16 + 4 + 2 for the various SpecialCells values

For example If column A contained text and Column D contained numbers, delete row?

One way is to use something like the code below

Sub Test2Columns()
    Dim i As Long
    Application.ScreenUpdating = False

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

        If Application.WorksheetFunction.IsText(Cells(i, "A")) = True And IsNumeric(Cells(i, "D")) Then _
                                                                                                   Cells(i, "A").EntireRow.Delete

    Next

End Sub

 

[PippaThePointer]

If you were adding the SpecialCells values together then for example for text, logical and errors you would use

Columns("D").SpecialCells(xlCellTypeConstants, xlTextValues + xlLogical + xlErrors).EntireRow.Delete
'or
Columns("D").SpecialCells(2, 22).EntireRow.Delete
' the 22 is 16 + 4 + 2 for the various SpecialCells values

One way is to use something like the code below

Sub Test2Columns()
    Dim i As Long
    Application.ScreenUpdating = False

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

        If Application.WorksheetFunction.IsText(Cells(i, "A")) = True And IsNumeric(Cells(i, "D")) Then _
                                                                                                   Cells(i, "A").EntireRow.Delete

    Next

End Sub

Thanks. I have added that in and it works.

 

[MARK858]

You're welcome

 

[PippaThePointer]

If you were adding the SpecialCells values together then for example for text, logical and errors you would use

Columns("D").SpecialCells(xlCellTypeConstants, xlTextValues + xlLogical + xlErrors).EntireRow.Delete
'or
Columns("D").SpecialCells(2, 22).EntireRow.Delete
' the 22 is 16 + 4 + 2 for the various SpecialCells values

One way is to use something like the code below

Sub Test2Columns()
    Dim i As Long
    Application.ScreenUpdating = False

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

        If Application.WorksheetFunction.IsText(Cells(i, "A")) = True And IsNumeric(Cells(i, "D")) Then _
                                                                                                   Cells(i, "A").EntireRow.Delete

    Next

End Sub

One more question.
Is there a way i can start the from Row 2 and go down? Ignoring the first row as a header? Like a 'StartRow = 2'.

 

[MARK858]

It already stops at row 2

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

Is there a way i can start the from Row 2 and go down?

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