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

.