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