Macro or IF statement??? HELP!

[Julia Easter]

Hi - I've got a stack of customer data that's been
extracted from a database in CSV format and then opened in
Excel

All's well apart from some of the accounts I dont need -
these are one's that have no deatils in the "Bank" "Sort
Code" "Account" columns.

My question is how do I go about getting Excel to look at
the blanks and automatically Delete them?

So far my little knowledge allowed me to make the
following if statement but it only tells me to
physically "DELETE" non-bank customers... I want to know
how I get to tell Excel to actually search for bloanks
then delete.... if that makes sence!!

HELP
Ta
Julia

 

[Guest]

VBA (Macro) is the way to go but more details on your spreadsheets layout will be required if you want assistance with code. In the mean time, Record a macro to execute some of the events you want to accomplish and take a look at the code asociated with it so u have an idea of how it looks and works.

 

[Norman Jones]

Hi Julia,

You can do this manually:

F5 key | Special | Blanks | ok
Edit | Delete | Entire Row | ok

If you need a programming solution, try:

Sub DelNoSortCodes()
Dim Rng As Range

Set Rng = Range("B2:B1000") ' <-- Adjust to your
SortCode Range

On Error Resume Next
Rng.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

 

[Guest]

This is a wholesale approach: if your just looking for any blank cell, but if you have intermittent blank cells within your range, but are still part of a valid account record they will be deleted too. So be cautious how you use this. If you need to have a set of criteria before you delete a row more sophisticated code is required
----- Norman Jones wrote: ----

Hi Julia

You can do this manually

F5 key | Special | Blanks | o
Edit | Delete | Entire Row | o

If you need a programming solution, try

Sub DelNoSortCodes(
Dim Rng As Rang

Set Rng = Range("B2:B1000") ' <-- Adjust to you
SortCode Rang

On Error Resume Nex
Rng.SpecialCells(xlBlanks).EntireRow.Delet
On Error GoTo
End Su

 

[Norman Jones]

Hi Chris,

Julia said:

"All's well apart from some of the accounts I dont need -
these are one's that have no deatils in the "Bank" "Sort"

I stand by my post.

---
Regards,
Norman

This is a wholesale approach: if your just looking for any blank cell,

but if you have intermittent blank cells within your range, but are still
part of a valid account record they will be deleted too. So be cautious how
you use this. If you need to have a set of criteria before you delete a row
more sophisticated code is required.

 

[Norman Jones]

Hi Chris,

Re-reading my response to Julia, I see that my manual solution omitted the
necessary first line:

Select the Sort Code Range

as shown explicitly in the VBA solution.

My apologies.

---
Regards
Norman

This is a wholesale approach: if your just looking for any blank cell,

but if you have intermittent blank cells within your range, but are still
part of a valid account record they will be deleted too. So be cautious how
you use this. If you need to have a set of criteria before you delete a row
more sophisticated code is required.

 

[Norman Jones]

Hi Julia,

For the manual solution, you need to initially select the Sort Code range
and therefore the manual instructions should read:

Select your Sort Code range
F5 key | Special | Blanks | ok
Edit | Delete | Entire Row | ok

 

[Guest]

Actually she said: "these are one's that have no deatils in the "Bank" "Sor
Code" "Account" columns."
Which means that those 3 columns are blank, but there could possibly be other columns., also your not sure if a record may have data in one and not in another but yet still be valid. OPs don't always give the full picture so i'm not quick to give them quick easy solutions that could wipe out their data


----- Norman Jones wrote: ----

Hi Chris

Julia said

"All's well apart from some of the accounts I dont need
these are one's that have no deatils in the "Bank" "Sort

I stand by my post

--
Regards
Norma

This is a wholesale approach: if your just looking for any blank cell

but if you have intermittent blank cells within your range, but are stil
part of a valid account record they will be deleted too. So be cautious ho
you use this. If you need to have a set of criteria before you delete a ro
more sophisticated code is required

 

[Guest]

Thanks for all the help - have it sussed now. Spreadsheet
working beautifully!!

Cheers for that!
Julia