http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://support.microsoft.com/support...01/default.asp
Microsoft Excel for Windows -- Visual Basic for Applications 101


http://www.mvps.org/dmcritchie/excel....htm#tutorials

vba tutorials are after the excel tutorials.

--
Regards,
Tom Ogilvy

"Lillian" wrote in message
...
Dave:

Thanks for all your effort, I did use the adding code
you gave to me, can not tell is fast or not, anyway,
thank you so much.

Dave, where I can get good excel Macro on the web that
way I can learn more?

Lillian
-----Original Message-----
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
A1:D1600. 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).






Lillian wrote:

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
nicely, you could use:

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

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: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 Peterson

.

.


--

Dave Peterson

.