Delete Rows Based on NOT Matching Two Criteria 1

[GSCaupling]

Using Win 7 & Excel 2013

I'm totally new to VBA code, but I need some kind of code/macro/magic that will delete rows in Excel.

Each month I import a financial statement with about 500 rows and I need to delete all but 350 of those rows. The rows I want to keep have an account number in Column D that begins with B and is followed by five digits (B12345). Some entries in Column D begin with a valid account number but are followed by other text (B12345~Smith, John).

I can identify the keepers with AND(Left(D1,1)="B",LEN(D1)=6). I want to identify and delete every row that does NOT meet these two criteria.

Thanks,
GS

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[PHV]

OR(Left(D1,1)<>"B",LEN(D1)<>6)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GSCaupling]

Thanks PHV.

While I needed that, what I need also is how to put this into a macro. I Googled this issue before posting and found answers all over the place, with loops and without, etc., but none that addressed two criteria in the same column.

Any ideas on what the macro would look like?

Thanks!

[Green]******^*******
[small]I[/small] [small]Hate[/small] [&hearts;] [small]Ambiguity.[/small][/green]

 

[SkipVought]

Hi,

I wouldn't delete anything. Rather I'd use MS Query to return the rows you want, that is assuming that your data is a proper table. No VBA required.


Using MS Query to get data from Excel faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GSCaupling]

That never crossed my mind, Skip. Thanks for the idea.


Now here is the rest of the story, so you can throw rocks at me for leaving out important stuff.

My goal is to get the Excel data into a compact table (no blank rows) so I can import it into Access. When I do it manually it involves deleting the rows described above, deleting columns that are not needed, converting some text to numbers, and adding proper headers.

Is MS Query still best solution, or can all of this be done directly by Access? Also, I'm not sure what you mean by a "proper table."

Thanks.

[Green]******^*******
[small]I[/small] [small]Hate[/small] [&hearts;] [small]Ambiguity.[/small][/green]

 

[SkipVought]

What are LIST & TABLE PRINCIPLES for Spreadsheet Users faq68-5184

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GSCaupling]

Skip, what I am trying to do is create a proper table from the imported data.

Picture a financial statement with page and column headers on every page, plus subtotal sections every few pages. Now picture all of that imported into Excel. I'm trying to delete every row that does NOT contain a valid account number (B12345) in column D.

Because some entries in Column D contain duplicate account numbers followed by additional text, it isn't as simple as sorting on Column D because these "extra text" entries will be mixed in.

Thanks for your time.

[Green]******^*******
[small]I[/small] [small]Hate[/small] [&hearts;] [small]Ambiguity.[/small][/green]

 

[SkipVought]

Reports are notoriously bad candidates for importing into excel without conditioning. I'd move heaven and earth to find the data source that this report was generated from!

Short of that, I'd analyse the data to find a foolproof method of identifying rows that do not belong in the table. Once you have a process nailed down, turn on your macro recorder & record doing the cleanup process. Verify that it works each month until you are confident in the cleanup process.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWD]

Good afternoon. As ever, sterling work from PHV & Skip. I have just followed on from the original post as it may give you more ideas when it comes to solving similar issues in the future; not necessarily the "best" or most elegant solutions but just maybe something when you really need it.

If you just apply the logic that what you wanted was not that these criteria were true you can have this formula:

=NOT(AND(LEFT(D1,1)="B",LEN(D1)=6))

Follwing Skip's suggestion (and taking on-board his other comments!), if you put this formula in cell G1 and record a macro you get something like:

Range("G1").Select
ActiveCell.FormulaR1C1 = "=NOT(AND(LEFT(RC[-3],1)=""B"",LEN(RC[-3])=6))"

From which you can amend it to be:

Range("G1").FormulaR1C1 = "=NOT(AND(LEFT(RC4,1)=""B"",LEN(RC4)=6))"

Which will populate G1 with:

=NOT(AND(LEFT($D1,1)="B",LEN($D1)=6))

It would then be up to you to decide how to proceed from there.

Many thanks,
D€$

 

[GSCaupling]

PWD - thank you!

[Green]******^*******
[small]I[/small] [small]Hate[/small] [&hearts;] [small]Ambiguity.[/small][/green]