I have one excel spreed sheet, it about 30,000 records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows is
space, the record will be delete, if ColumsA has "----",
the record will be delete, if column(F) has space, the
record will be delete.

How can I write the macro to delete those record, thanks
for the help.

Lillian

Try this(is not fast)

I don't know what you mean by space?
You can add things to the macro yourself

Sub Test()
Dim r As Long
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Then
.Rows(r).Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Lillian" wrote in message ...
I have one excel spreed sheet, it about 30,000 records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows is
space, the record will be delete, if ColumsA has "----",
the record will be delete, if column(F) has space, the
record will be delete.

How can I write the macro to delete those record, thanks
for the help.

Lillian

I forgot to type the screenupdating

Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Try this(is not fast)

I don't know what you mean by space?
You can add things to the macro yourself

Sub Test()
Dim r As Long
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Then
.Rows(r).Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Lillian" wrote in message ...
I have one excel spreed sheet, it about 30,000 records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows is
space, the record will be delete, if ColumsA has "----",
the record will be delete, if column(F) has space, the
record will be delete.

How can I write the macro to delete those record, thanks
for the help.

Lillian

I use your original code, all the "problem" "-----"
records is gone, but I still have a lots of empty row,
how can I delete them, also some of records on columnF
has spaces " ", how can I delete them, thanks for all the
help, you are a genius.

Can I use the following code:

Sub Test()
Dim r As Long
Dim s As String
Application.ScreenUpdating = False
With Worksheets("sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----------" Or _
.Cells(r, "A").Value = "PROBLEM" Or _
.Cells(s, "A").Value = " " Or _
.Cells(s, "F").Value = " " Then
.Rows(r).Delete
.Rows(s).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub

thanks.

Lillian
-----Original Message-----
I forgot to type the screenupdating

Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" wrote in
message ...
Try this(is not fast)

I don't know what you mean by space?
You can add things to the macro yourself

Sub Test()
Dim r As Long
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Then
.Rows(r).Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Lillian" wrote in message
...
I have one excel spreed sheet, it about 30,000
records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows is
space, the record will be delete, if ColumsA has "---
-",
the record will be delete, if column(F) has space,
the
record will be delete.

How can I write the macro to delete those record,
thanks
for the help.

Lillian





.

See this site for a macro te trim your data

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Then use this

Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Or _
.Cells(r, "A").Value = "" Or _
.Cells(r, "A").Value = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



wrote in message ...
I use your original code, all the "problem" "-----"
records is gone, but I still have a lots of empty row,
how can I delete them, also some of records on columnF
has spaces " ", how can I delete them, thanks for all the
help, you are a genius.

Can I use the following code:

Sub Test()
Dim r As Long
Dim s As String
Application.ScreenUpdating = False
With Worksheets("sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----------" Or _
.Cells(r, "A").Value = "PROBLEM" Or _
.Cells(s, "A").Value = " " Or _
.Cells(s, "F").Value = " " Then
.Rows(r).Delete
.Rows(s).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub

thanks.

Lillian
-----Original Message-----
I forgot to type the screenupdating

Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" wrote in
message ...
Try this(is not fast)

I don't know what you mean by space?
You can add things to the macro yourself

Sub Test()
Dim r As Long
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Then
.Rows(r).Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Lillian" wrote in message
...
I have one excel spreed sheet, it about 30,000
records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows is
space, the record will be delete, if ColumsA has "---
-",
the record will be delete, if column(F) has space,
the
record will be delete.

How can I write the macro to delete those record,
thanks
for the help.

Lillian





.

What you meant by trim data? I went to this web site, I
am not sure how to do, please help.

thanks.

Lillian

-----Original Message-----
See this site for a macro te trim your data

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Then use this

Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Or _
.Cells(r, "A").Value = "" Or _
.Cells(r, "A").Value = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



wrote in message
...
I use your original code, all the "problem" "-----"
records is gone, but I still have a lots of empty row,
how can I delete them, also some of records on columnF
has spaces " ", how can I delete them, thanks for all
the
help, you are a genius.

Can I use the following code:

Sub Test()
Dim r As Long
Dim s As String
Application.ScreenUpdating = False
With Worksheets("sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----------" Or _
.Cells(r, "A").Value = "PROBLEM" Or _
.Cells(s, "A").Value = " " Or _
.Cells(s, "F").Value = " " Then
.Rows(r).Delete
.Rows(s).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub

thanks.

Lillian
-----Original Message-----
I forgot to type the screenupdating

Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" wrote in
message ...
Try this(is not fast)

I don't know what you mean by space?
You can add things to the macro yourself

Sub Test()
Dim r As Long
With Worksheets("Sheet1")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "A").Value = "----" Or _
.Cells(r, "A").Value = "problem" Then
.Rows(r).Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Lillian" wrote in message
...
I have one excel spreed sheet, it about 30,000
records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows
is
space, the record will be delete, if ColumsA
has "---
-",
the record will be delete, if column(F) has space,
the
record will be delete.

How can I write the macro to delete those record,
thanks
for the help.

Lillian





.



.

I'm not sure if you got your problem solved, but modifying Ron's code slightly:

Option Explicit
Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1
If Application.CountA(.Rows(r)) = 0 _
Or .Cells(r, "A").Value = "----" _
Or LCase(.Cells(r, "A").Value) = "problem" _
Or Trim(.Cells(r, "F").Value) = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Lillian wrote:

I have one excel spreed sheet, it about 30,000 records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows is
space, the record will be delete, if ColumsA has "----",
the record will be delete, if column(F) has space, the
record will be delete.

How can I write the macro to delete those record, thanks
for the help.

Lillian

--

Dave Peterson

Dave:

I try yours, it work perfectly, thank you so much.

Lillian
-----Original Message-----
I'm not sure if you got your problem solved, but
modifying Ron's code slightly:

Option Explicit
Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows
(.UsedRange.Rows.Count).Row To 1 Step -1
If Application.CountA(.Rows(r)) = 0 _
Or .Cells(r, "A").Value = "----" _
Or LCase(.Cells(r, "A").Value) = "problem" _
Or Trim(.Cells(r, "F").Value) = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Lillian wrote:

I have one excel spreed sheet, it about 30,000
records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows is
space, the record will be delete, if ColumsA has "----
",
the record will be delete, if column(F) has space, the
record will be delete.

How can I write the macro to delete those record,
thanks
for the help.

Lillian

--

Dave Peterson

.

Dave:

Now I have all the data I need, is anyway to merge
columnD,ColumnE,ColumnF together, how to write the macro
to do that, I try to use excel merge cell, it did not
work, because columnD,E,F has different type of data.

thanks for all the help.

Lillian
-----Original Message-----
Dave:

I try yours, it work perfectly, thank you so much.

Lillian
-----Original Message-----
I'm not sure if you got your problem solved, but
modifying Ron's code slightly:

Option Explicit
Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows
(.UsedRange.Rows.Count).Row To 1 Step -1
If Application.CountA(.Rows(r)) = 0 _
Or .Cells(r, "A").Value = "----" _
Or LCase(.Cells(r, "A").Value) = "problem"
_
Or Trim(.Cells(r, "F").Value) = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Lillian wrote:

I have one excel spreed sheet, it about 30,000
records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows is
space, the record will be delete, if ColumsA has "----
",
the record will be delete, if column(F) has space, the
record will be delete.

How can I write the macro to delete those record,
thanks
for the help.

Lillian

--

Dave Peterson

.

.

Do you mean merge like in merge cells or do you mean merge like in concatenate?

I'm gonna guess concatenate.

I'd insert a new column D and use a formula that did the concatenation, then
convert to values, then delete the original D:F (now E:G).

Option Explicit
Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row
.Columns("D:D").Insert
With .Range("d1:d" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2] & "" "" & RC[3]"
'.Value = .Value
End With
.Range("e:g").EntireColumn.Hidden = True
'.Range("e:g").EntireColumn.Delete
End With
End Sub

I used the last row of column D to determine the last row to get a formula.
Change this to a column that always has data.

And I don't know what's in those columns, but it's not always best to
concatenate and delete. As you can see it's really easy to put things
together. It's not always as easy to separate them into the correct fields.

It might be better to leave the formulas and just hide the columns that the
formulas use.

========
Did you really mean Merge (as in Format|Cells|Aligment tab)?

Then maybe something like:

Option Explicit
Sub testme02()
Dim LastRow As Long
Dim iRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row

For iRow = 1 To LastRow
With .Cells(iRow, "D")
.Value = .Value _
& " " & .Offset(0, 1).Value _
& " " & .Offset(0, 2).Value
.Offset(0, 1).Resize(1, 2).ClearContents
End With
Next iRow

.Range("d1:f" & LastRow).Merge across:=True
End With
End Sub

And one last thing, if your data needs to be formatted nicely, you could use:

.Value = .Text _
& " " & .Offset(0, 1).Text _
& " " & .Offset(0, 2).Text

to pick up the format from the cell.

Or you could specify the format you want:
.Value = .Value _
& " " & format(.Offset(0, 1).Value,"mm/dd/yyyy") _
& " " & format(.Offset(0, 2).Value,"$0.00")
with the Format function.

Inside the worksheet, you'd use something like: =text(a1,"$0.00")



Lillian wrote:

Dave:

Now I have all the data I need, is anyway to merge
columnD,ColumnE,ColumnF together, how to write the macro
to do that, I try to use excel merge cell, it did not
work, because columnD,E,F has different type of data.

thanks for all the help.

Lillian
-----Original Message-----
Dave:

I try yours, it work perfectly, thank you so much.

Lillian
-----Original Message-----
I'm not sure if you got your problem solved, but
modifying Ron's code slightly:

Option Explicit
Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows
(.UsedRange.Rows.Count).Row To 1 Step -1
If Application.CountA(.Rows(r)) = 0 _
Or .Cells(r, "A").Value = "----" _
Or LCase(.Cells(r, "A").Value) = "problem"
_
Or Trim(.Cells(r, "F").Value) = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Lillian wrote:

I have one excel spreed sheet, it about 30,000
records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows is
space, the record will be delete, if ColumsA has "----
",
the record will be delete, if column(F) has space, the
record will be delete.

How can I write the macro to delete those record,
thanks
for the help.

Lillian

--

Dave Peterson

.

.


--

Dave Peterson

Dave:

I use the concatenate one for the test,it work out
perfect, all the data is on D column, if I click D
column, it is refering to =E1&" "&F1&" "&G1, if I remove
the '.Range("e:g").EntireColumn.Delete again, guest what
the D column become like #REF, is anyway after we run
this then do somekind of pastspecial in H column with
value, but with macro script?

Your are a genius. thanks for all the effort.

Lillian

-----Original Message-----
Do you mean merge like in merge cells or do you mean
merge like in concatenate?

I'm gonna guess concatenate.

I'd insert a new column D and use a formula that did the
concatenation, then
convert to values, then delete the original D:F (now
E:G).

Option Explicit
Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row
.Columns("D:D").Insert
With .Range("d1:d" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3]"
'.Value = .Value
End With
.Range("e:g").EntireColumn.Hidden = True
'.Range("e:g").EntireColumn.Delete
End With
End Sub

I used the last row of column D to determine the last
row to get a formula.
Change this to a column that always has data.

And I don't know what's in those columns, but it's not
always best to
concatenate and delete. As you can see it's really easy
to put things
together. It's not always as easy to separate them into
the correct fields.

It might be better to leave the formulas and just hide
the columns that the
formulas use.

========
Did you really mean Merge (as in Format|Cells|Aligment
tab)?

Then maybe something like:

Option Explicit
Sub testme02()
Dim LastRow As Long
Dim iRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row

For iRow = 1 To LastRow
With .Cells(iRow, "D")
.Value = .Value _
& " " & .Offset(0, 1).Value _
& " " & .Offset(0, 2).Value
.Offset(0, 1).Resize(1, 2).ClearContents
End With
Next iRow

.Range("d1:f" & LastRow).Merge across:=True
End With
End Sub

And one last thing, if your data needs to be formatted
nicely, you could use:

.Value = .Text _
& " " & .Offset(0, 1).Text _
& " " & .Offset(0, 2).Text

to pick up the format from the cell.

Or you could specify the format you want:
.Value = .Value _
& " " & format(.Offset(0,
1).Value,"mm/dd/yyyy") _
& " " & format(.Offset(0,
2).Value,"$0.00")
with the Format function.

Inside the worksheet, you'd use something like: =text
(a1,"$0.00")



Lillian wrote:

Dave:

Now I have all the data I need, is anyway to merge
columnD,ColumnE,ColumnF together, how to write the
macro
to do that, I try to use excel merge cell, it did not
work, because columnD,E,F has different type of data.

thanks for all the help.

Lillian
-----Original Message-----
Dave:

I try yours, it work perfectly, thank you so much.

Lillian
-----Original Message-----
I'm not sure if you got your problem solved, but
modifying Ron's code slightly:

Option Explicit
Sub Test()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows
(.UsedRange.Rows.Count).Row To 1 Step -1
If Application.CountA(.Rows(r)) = 0 _
Or .Cells(r, "A").Value = "----" _
Or LCase(.Cells(r, "A").Value)
= "problem"
_
Or Trim(.Cells(r, "F").Value) = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Lillian wrote:

I have one excel spreed sheet, it about 30,000
records, I
need to deleted some of records, if columnsA
has "problem", the record will be delete, if rows
is
space, the record will be delete, if ColumsA has "-
---
",
the record will be delete, if column(F) has space,
the
record will be delete.

How can I write the macro to delete those record,
thanks
for the help.

Lillian

--

Dave Peterson

.

.


--

Dave Peterson

.