Macro or IF statement??? HELP!
 

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

Macro or IF statement??? HELP!
 

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.

Macro or IF statement??? HELP!
 

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

---
regards,
Norman


"Julia Easter" wrote in message
...
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

Macro or IF statement??? HELP!
 

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

--
regards
Norma


"Julia Easter" wrote in messag
..
Hi - I've got a stack of customer data that's bee
extracted from a database in CSV format and then opened i
Exce
All's well apart from some of the accounts I dont need
these are one's that have no deatils in the "Bank" "Sor
Code" "Account" columns
My question is how do I go about getting Excel to look a
the blanks and automatically Delete them
So far my little knowledge allowed me to make th
following if statement but it only tells me t
physically "DELETE" non-bank customers... I want to kno
how I get to tell Excel to actually search for bloank
then delete.... if that makes sence!
HEL
T
Juli

Macro or IF statement??? HELP!
 

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

"chris" wrote in message
...
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 | 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

---
regards,
Norman


"Julia Easter" wrote in message
...
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

Macro or IF statement??? HELP!
 

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

"chris" wrote in message
...
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 | 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

---
regards,
Norman


"Julia Easter" wrote in message
...
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

Macro or IF statement??? HELP!
 

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

---
Regards,
Norman


"Norman Jones" wrote in message
...
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

---
regards,
Norman


"Julia Easter" wrote in message
...
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

Macro or IF statement??? HELP!
 

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

"chris" wrote in messag
..
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
----- 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
--
regards
Norma
"Julia Easter" wrote in messag
..
Hi - I've got a stack of customer data that's bee
extracted from a database in CSV format and then opened i
Exce
All's well apart from some of the accounts I dont need
these are one's that have no deatils in the "Bank" "Sor
Code" "Account" columns
My question is how do I go about getting Excel to look a
the blanks and automatically Delete them
So far my little knowledge allowed me to make th
following if statement but it only tells me t
physically "DELETE" non-bank customers... I want to kno
how I get to tell Excel to actually search for bloank
then delete.... if that makes sence!
HEL
T
Juli

Macro or IF statement??? HELP!
 

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

Cheers for that!
Julia
-----Original Message-----
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

"chris" wrote in
message
...
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 | 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

---
regards,
Norman


"Julia Easter" wrote in
message
...
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





.