Using SpecialCells to return rows for deletion
 

Hi,

I have a table (in MS Excel) with values like this

LocalCurrency, Amt, BaseCurrency, ValueDt, TestColumn

I want to delete all rows where the column LocalCurrency=BaseCurrency

Of course I can create a loop through all the cells and delete the rows, but
I want a one line option if possible.

I have a column where a test is done that returns TRUE or FALSE if the
currencies match, but how can I use SpecialCells to return all those rows
where it is True and delete them?

I saw something along these lines a couple of months back but now cannot
find it..

thanks for any tips, or showing me the way

Philip

Using SpecialCells to return rows for deletion
 

Hi,

I am trying to use something like this:

Range("F2",
Range("F2").End(xlDown).Address).SpecialCells(xlCe llTypeConstants,
"TRUE").EntireRow.Delete

but I get an error message - how can I get this to work.

Thanks for any help

Philip
"Philip" wrote:

Hi,

I have a table (in MS Excel) with values like this

LocalCurrency, Amt, BaseCurrency, ValueDt, TestColumn

I want to delete all rows where the column LocalCurrency=BaseCurrency

Of course I can create a loop through all the cells and delete the rows, but
I want a one line option if possible.

I have a column where a test is done that returns TRUE or FALSE if the
currencies match, but how can I use SpecialCells to return all those rows
where it is True and delete them?

I saw something along these lines a couple of months back but now cannot
find it..

thanks for any tips, or showing me the way

Philip

Using SpecialCells to return rows for deletion
 

Found it!

Range("F2", Range("F2").End(xlDown).Address).FormulaR1C1 =
"=IF(RC[-1]<RC[-3],""OK"",NA())"
If Application.WorksheetFunction.CountA("#N/A") 0 Then
Range("F2",
Range("F2").End(xlDown).Address).SpecialCells(xlCe llTypeFormulas,
xlErrors).EntireRow.Delete
End If

But
thanks anyway

Philip

"Philip" wrote:

Hi,

I have a table (in MS Excel) with values like this

LocalCurrency, Amt, BaseCurrency, ValueDt, TestColumn

I want to delete all rows where the column LocalCurrency=BaseCurrency

Of course I can create a loop through all the cells and delete the rows, but
I want a one line option if possible.

I have a column where a test is done that returns TRUE or FALSE if the
currencies match, but how can I use SpecialCells to return all those rows
where it is True and delete them?

I saw something along these lines a couple of months back but now cannot
find it..

thanks for any tips, or showing me the way

Philip