delete record using Macro in excel spreed sheet

[Lillian]

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

 

[Ron de Bruin]

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

 

[Ron de Bruin]

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

 

[Guest]

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)




"Ron de Bruin" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

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

 

[Lillian]

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

thanks.

Lillian

 

[Ron de Bruin]

When you use this macro it will remove all spaces in the data.
A cell with a space in it is not Empty

Select your data first and run this macro

Sub TRIMALL_alternate()
'Dave Peterson, programming, 2002-08-17
'http://google.com/[email protected]
Dim myRange As Range
Dim myCol As Range

Set myRange = Intersect(ActiveSheet.UsedRange, Selection)
If myRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False

myRange.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

For Each myCol In myRange.Columns
If Application.CountA(myCol) > 0 Then
myCol.TextToColumns Destination:=myCol(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next myCol
Application.ScreenUpdating = True
End Sub

Now run the macro I posted

 

[Lillian]

I run this macro first, nothing happen, what suppose
happen after I run?
then I run the following

Sub Test()
nothing happen, all the empty row still out there, also
the record with columnF has value " " still exist, never
got delete either, what did I do wrong.

Lillian

 

[Ron de Bruin]

Select your data first and run this macro

You not have select your data I think

 

[Lillian]

What you mean by select data first, my excel sheet was
open, then from the tool--> micro-->TRIMALL_alternate,
the macro you gave to me, what did I do wrong.

 

[Dave Peterson]

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

 

[Ron de Bruin]

Hi

The TRIMALL macro is working on all the cells you have select
Select the cells in the Column A:F and try it

You can also use Dave solution

 

[Lillian]

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

 

[Lillian]

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

 

[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").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]

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").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")

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 = "problem"
_

--

Dave Peterson
(e-mail address removed)
.

 

[Dave Peterson]

If you want to delete those columns, then you have to uncomment this line (just
get rid of the leading apostrophe):

'.Value = .Value

It's essentially copy|paste special|values.

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").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")

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
(e-mail address removed)
.

.

--

Dave Peterson
(e-mail address removed)
.

 

[Guest]

Thanks Dave, your the greatest. I can not thank you
enough.

Lillian

-----Original Message-----
If you want to delete those columns, then you have to uncomment this line (just
get rid of the leading apostrophe):

'.Value = .Value

It's essentially copy|paste special|values.

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").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
(e-mail address removed)
.

.


--

Dave Peterson
(e-mail address removed)
.

--

Dave Peterson
(e-mail address removed)
.

 

[Lillian]

Dave:

I use that merge macro, it take a while to run, not like
concatenate one, it only one second, then I got the
result I need. thanks.

I have one more question when you said in previous email:

if your data needs to be formatted
how do I write the macro to formatted D E F columns to
Text, before I run your merge macro script, maybe data in
column D,E,F did not formatted as you said, that why take
a long time to run, as you mention as above. did this
formatted macro need to run separately or they can
combined into merge marco?

Thank you again, your are wonderful.

Lillian

-----Original Message-----
Thanks Dave, your the greatest. I can not thank you
enough.

Lillian

-----Original Message-----
If you want to delete those columns, then you have to uncomment this line (just
get rid of the leading apostrophe):

'.Value = .Value

It's essentially copy|paste special|values.

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").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
(e-mail address removed)
.

.


--

Dave Peterson
(e-mail address removed)
.

--

Dave Peterson
(e-mail address removed)
.

.

 

[Dave Peterson]

the easy question first.

An example showing the difference between the .value and .text.

Say you have 123456.323 in A1. But you have it formatted as "$#,##0.00", it'll
show as: $123,456.32

So if I concatenate with .value, I lose all that nice formatting. But if I use
the .text, it'll show up just like it appears in the cell.

This is useful with dates, too. .value = 37931 could be formatted as a date
(mm/dd/yyyy) to show 11/06/2003.

(It's not really Format|Cells|Number tab|Text kind of formatting.)

I just ran a test of that merge version (testme02). I put simple data in
A11600. It ran pretty quickly.

You can speed it up by adding this to the top of the code:

Dim CalcMode As Long
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.screenupdating = false

and near the bottom:

Application.Calculation = CalcMode
Application.ScreenUpdating = True

It'll stop the flickering of the display, too (.screenupdating = false).

Dave:

I use that merge macro, it take a while to run, not like
concatenate one, it only one second, then I got the
result I need. thanks.

I have one more question when you said in previous email:

if your data needs to be formatted
how do I write the macro to formatted D E F columns to
Text, before I run your merge macro script, maybe data in
column D,E,F did not formatted as you said, that why take
a long time to run, as you mention as above. did this
formatted macro need to run separately or they can
combined into merge marco?

Thank you again, your are wonderful.

Lillian

-----Original Message-----
Thanks Dave, your the greatest. I can not thank you
enough.

Lillian

-----Original Message-----
If you want to delete those columns, then you have to uncomment this line (just
get rid of the leading apostrophe):

'.Value = .Value

It's essentially copy|paste special|values.


Lillian wrote:

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").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
(e-mail address removed)
.

.


--

Dave Peterson
(e-mail address removed)
.


--

Dave Peterson
(e-mail address removed)
.

.