Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing rows and deletion | Excel Programming | |||
On deletion of rows | Excel Discussion (Misc queries) | |||
Prn file carriage return deletion within notepad | Excel Programming | |||
Problems copying Autofiltered rows & SpecialCells | Excel Programming | |||
Help to hide rows, SpecialCells(xlCellTypeBlanks) doesn't work... | Excel Programming |