Convert text to data and remove specific lines

[SubwAy]

Hi all,

I've managed to come up with two macros which I feel are extremely close to working but somehow are still glitchy, and I was hoping you'd be able to help me There are both on the same sheet to help me remove duplicates.

First one: converting a text field into a date. The current format (which I cannot change as it is an extract of Oracle) is, for example, 11-JAN-17. When using the macro below, I get the right conversion for 11-Jan but the year turns to 2011.

Please Login or Register  to view this content.

Second one: I am trying to remove all lines for which my formula in column AF (of a Table, ie an actual table created by Excel with auto formatting, ranges, etc) returns TRUE. When using the macro, I lose all lines!

Please Login or Register  to view this content.

Thanks in advance to anyone who can help.
IM

[ImranBhatti]

Please upload a sample workbook.

[SubwAy]

Sorry about that, I just included one

Thanks.

[ImranBhatti]

Second macro is modified

Please Login or Register  to view this content.

[SubwAy]

This works perfectly, thank you

I just need to figure out how to convert my dates!

[ImranBhatti]

Please Login or Register  to view this content.

There problem is not with the macro. Problem seems to be with your extracted data from Oracle.
You need to refresh all the cells of B column before running this data macro.


Then run this macro .
Hopefully it will work

Best Regards
Imran Bhatti

[SubwAy]

Hi Imran,

How would you suggest I refresh all the cells in column B? Is this something I can build into the macro?

Thanks,
IM

[ImranBhatti]

Hi Imran,

How would you suggest I refresh all the cells in column B? Is this something I can build into the macro?

Thanks,
IM

Try this instead

Please Login or Register  to view this content.

[SubwAy]

This is perfect, thank you

[SubwAy]

ImranBhatti, can I ask why you use the XFD1 range in your macro and how it works in my case? I'm just curious to understand so I can replicate it again.

Thanks,

[ImranBhatti]

Simple
In order to refresh your range of extracted data, I multiplied the column B with 1.and this 1 was taken from a cell that was far far away from your original data.This was done by using the PasteSpecial method (you might be familiar with).