OK, I'm a sloppy sloppy editor. So sorry! One last try.

Dim R As Integer, MyRg As Range

For R = 1 To 100
Set MyRg = Cells(R, 8)
If MyRg < "F" And MyRg < "M" Then
MyRg.Delete Shift:=xlToLeft
End If
Next


"slarbie" wrote:

oops, I missed one when I edited shortened the variable name to MyRg. should
be:

dim R as integer, MyRg as range

for R = 1 to 100
set MyRg = cells(R, 8)
if MyRg < "F" and MyRg < "M" then
MyRange.delete shift:=xlLeft
end if
next

Also, changed the column to "8" to match your described scenario with column
"H". If it's possible for gender to be blank, you'd want to add "and MyRg <
""" to the "if" line.



"slarbie" wrote:

if your gender field is to the right of the middle name field that's parsing
out incorrectly, you could just do this. my example assumes gender is
supposed to be in column E (5), but has ended up in column F because the
middle name parsed out into too many pieces. With 100 records starting in
row 1. change all those bits as needed

dim R as integer, MyRg as range

for R = 1 to 100
set MyRange = cells(r, 5)
if MyRg < "F" and MyRg < "M" then
MyRange.delete shift:=xlLeft
end if
next

Hope that helps!


"Diddy" wrote:

Hi Joel,

The database was SQL I believe but out of our control, we only receive data
in CSV format.

So, no go on this one, That's a shame, thought I could reduce the tedium!.
Although the data is not random as such. It's always where a person has a
third forename. Col G is forename and Col H is Middle name. Problem seems to
occur when inputter has put two names into the Middle Name field in the DB.

Thank you anyway Joel :-)

"joel" wrote:

What type of database did the datta come from and what method was used to do
the exporting. If you have random placement of data excel macros can't help
fix the spreadsheet. There may be ways of fixing the problem before you put
the data in the spreadsheet, but I need more Info.

"Diddy" wrote:

Hi everyone,

I have no idea how to even make a start on this one.
I have worksheets containing info exported from a database. On some rows
(don't ask me how it happens !) values that should be in col H have over- run
and are in col I and then all the data has shifted 1 column to the right. Col
I is Gender and correct values should therefore be only F or M.
At the moment I use autofilter and filter to the incorrect values then cut
and paste contents of I to AN then cut and paste J:AN back to a range
starting with I.

Is there any way I can automate this with a macro?

Many thanks