I need some help cleaning up a spread sheet. I have several worksheets with
data on them. I would like to delete any rows of data that don't contain one
of 3 or 4 specific values in column F of that row. For example if column F of
row 1 does not contain the word "RED", "WHITE" or the word "BLUE" then delete
the row. Also, some of the columns have spaces in the beginning and at the
end of the actual value that I need removed at the same time. For example a
field may have " Free Checking is Here " and I need all of the
spaces on the end of that value removed but retaining the spaces between the
words. Lastly, row 1 on all of the sheets contains a header row. Any help is
GREATLY appreciated!! Thanks in advance.

This macro will do what you want. I assumed your data went from A2 to Hx
where "x" is any number you wish. Change this as needed to fit your data.
HTH Otto
Sub CleanUp()
Dim RngColA As Range
Dim i As Range
Dim c As Long
Application.ScreenUpdating = False
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = RngColA.Count To 1 Step -1
If RngColA(c).Offset(, 5) < "RED" And _
RngColA(c).Offset(, 5) < "WHITE" And _
RngColA(c).Offset(, 5) < "BLUE" Then _
RngColA(c).EntireRow.Delete
Next c
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA.Resize(, 8)
i.Value = Application.Trim(i.Value)
Next i
Application.ScreenUpdating = True
End Sub
"hshayh0rn" wrote in message
...
I need some help cleaning up a spread sheet. I have several worksheets with
data on them. I would like to delete any rows of data that don't contain
one
of 3 or 4 specific values in column F of that row. For example if column F
of
row 1 does not contain the word "RED", "WHITE" or the word "BLUE" then
delete
the row. Also, some of the columns have spaces in the beginning and at the
end of the actual value that I need removed at the same time. For example
a
field may have " Free Checking is Here " and I need all of
the
spaces on the end of that value removed but retaining the spaces between
the
words. Lastly, row 1 on all of the sheets contains a header row. Any help
is
GREATLY appreciated!! Thanks in advance.

Thanks Otto but the column I need evaluated is actually E so can you tell me
which value in your code I should change? When I ran this code it deleted
everything except the first row. Also, I'm sure this is a lot more complex
but I was just informed that I may need to import the data several times in
the next few weeks and that really takes a long time so I'm wondering if
there is an easy way to code the import process. Here is the issue though.
The txt file is over 500,000 line which means I can't import it to a single
worksheet so I have been importing the data to a worksheet. Manually
stripping out the stuff I don't need (hence the previous request) and then
importing the next 65,536 records, striping out what I don't need. Doing a
cut of what's left and pasting it to the original worksheet (that now has
some room after I filter out what I don't need) and repeat the process until
I have 65,536 records of data I need in one sheet and then I begin all over
again with a new sheet until all 500,000 line of the txt file have been
copied and filtered. Any way to automate this?


"Otto Moehrbach" wrote:

This macro will do what you want. I assumed your data went from A2 to Hx
where "x" is any number you wish. Change this as needed to fit your data.
HTH Otto
Sub CleanUp()
Dim RngColA As Range
Dim i As Range
Dim c As Long
Application.ScreenUpdating = False
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = RngColA.Count To 1 Step -1
If RngColA(c).Offset(, 5) < "RED" And _
RngColA(c).Offset(, 5) < "WHITE" And _
RngColA(c).Offset(, 5) < "BLUE" Then _
RngColA(c).EntireRow.Delete
Next c
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA.Resize(, 8)
i.Value = Application.Trim(i.Value)
Next i
Application.ScreenUpdating = True
End Sub
"hshayh0rn" wrote in message
...
I need some help cleaning up a spread sheet. I have several worksheets with
data on them. I would like to delete any rows of data that don't contain
one
of 3 or 4 specific values in column F of that row. For example if column F
of
row 1 does not contain the word "RED", "WHITE" or the word "BLUE" then
delete
the row. Also, some of the columns have spaces in the beginning and at the
end of the actual value that I need removed at the same time. For example
a
field may have " Free Checking is Here " and I need all of
the
spaces on the end of that value removed but retaining the spaces between
the
words. Lastly, row 1 on all of the sheets contains a header row. Any help
is
GREATLY appreciated!! Thanks in advance.

Change the Offset(,5) to Offset(,4).
As written this macro will delete every row that doesn't have RED, WHITE, or
BLUE in Column F (now E). Then it Trims (removes spaces) in every cell in
the range that is left after the row deletions.
I can't help you with the import task. That depends on the source of the
data and I have not done importing. Otto
"hshayh0rn" wrote in message
...
Thanks Otto but the column I need evaluated is actually E so can you tell
me
which value in your code I should change? When I ran this code it deleted
everything except the first row. Also, I'm sure this is a lot more complex
but I was just informed that I may need to import the data several times
in
the next few weeks and that really takes a long time so I'm wondering if
there is an easy way to code the import process. Here is the issue though.
The txt file is over 500,000 line which means I can't import it to a
single
worksheet so I have been importing the data to a worksheet. Manually
stripping out the stuff I don't need (hence the previous request) and then
importing the next 65,536 records, striping out what I don't need. Doing a
cut of what's left and pasting it to the original worksheet (that now has
some room after I filter out what I don't need) and repeat the process
until
I have 65,536 records of data I need in one sheet and then I begin all
over
again with a new sheet until all 500,000 line of the txt file have been
copied and filtered. Any way to automate this?


"Otto Moehrbach" wrote:

This macro will do what you want. I assumed your data went from A2 to Hx
where "x" is any number you wish. Change this as needed to fit your
data.
HTH Otto
Sub CleanUp()
Dim RngColA As Range
Dim i As Range
Dim c As Long
Application.ScreenUpdating = False
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = RngColA.Count To 1 Step -1
If RngColA(c).Offset(, 5) < "RED" And _
RngColA(c).Offset(, 5) < "WHITE" And _
RngColA(c).Offset(, 5) < "BLUE" Then _
RngColA(c).EntireRow.Delete
Next c
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In RngColA.Resize(, 8)
i.Value = Application.Trim(i.Value)
Next i
Application.ScreenUpdating = True
End Sub
"hshayh0rn" wrote in message
...
I need some help cleaning up a spread sheet. I have several worksheets
with
data on them. I would like to delete any rows of data that don't
contain
one
of 3 or 4 specific values in column F of that row. For example if
column F
of
row 1 does not contain the word "RED", "WHITE" or the word "BLUE" then
delete
the row. Also, some of the columns have spaces in the beginning and at
the
end of the actual value that I need removed at the same time. For
example
a
field may have " Free Checking is Here " and I need all of
the
spaces on the end of that value removed but retaining the spaces
between
the
words. Lastly, row 1 on all of the sheets contains a header row. Any
help
is
GREATLY appreciated!! Thanks in advance.