About Us

Lillian[_3_]

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

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

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

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

.

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

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

.

Lillian[_3_]

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

.

.

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

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

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

"Lillian" wrote in message ...
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

.

.

Lillian[_3_]

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

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, "F").Value = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True

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

-----Original Message-----
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/groups?

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

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

"Lillian" wrote in message
...
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

.

.

.

Ron de Bruin

Select your data first and run this macro

You not have select your data I think

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

"Lillian" wrote in message ...
I run this macro first, nothing happen, what suppose
happen after I run?
then I run the following

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, "F").Value = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True

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

-----Original Message-----
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/groups?

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

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

"Lillian" wrote in message
...
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

.

.

.

Lillian[_3_]

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.

-----Original Message-----
Select your data first and run this macro

You not have select your data I think

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

"Lillian" wrote in message
...
I run this macro first, nothing happen, what suppose
happen after I run?
then I run the following

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, "F").Value = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True

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

-----Original Message-----
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/groups?

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

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

"Lillian" wrote in message
...
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

.

.

.

.

Dave Peterson[_3_]

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

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

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

"Lillian" wrote in message ...
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.

-----Original Message-----
Select your data first and run this macro

You not have select your data I think

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

"Lillian" wrote in message
...
I run this macro first, nothing happen, what suppose
happen after I run?
then I run the following

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, "F").Value = "" Then
.Rows(r).Delete
End If
Next
End With
Application.ScreenUpdating = True

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

-----Original Message-----
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/groups?

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

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

"Lillian" wrote in message
...
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

.

.

.

.

Lillian[_3_]

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

.

Lillian[_5_]

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[_3_]

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

Lillian[_5_]

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[_3_]

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

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

.

Lillian[_5_]

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[_3_]

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

Lillian[_5_]

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

.

Tom Ogilvy

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

.

Dave Peterson[_3_]

On top of what Tom Ogilvy wrote, I liked to lurk in these newsgroups.

And when you get a few bucks to spare, you may want to get a book:

Debra Dalgleish has a big ole list:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. John Green (and others) is nice,
too. See if you can find them in your local bookstore and you can choose what
one you like best.

Lillian wrote:

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

.

--

Dave Peterson

Lillian[_5_]

Dave:

I have another question, since I like your knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column from
columnS to columnAD, also if column is empty or columns
is "0" or column is "NA: need to delete as well. how to
write the macro for that.

2).I have another worksheet need to delete the columnR to
columnEE, except columnS and columnEF, how to write the
macro for this.

thanks for all the help.

Lillian

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

.

.

Dave Peterson[_3_]

the easy one first.

#2. Record a macro when you do it manually. I selected R, and T:EE, then
rightclick|Delete and got something that looked like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you mean that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column To .Range("S1").Column Step -1
If Application.CountA(.Columns(myCol)) = 0 Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union(delRng, .Cells(1, myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff. Are you checking one cell,
all cells or if any of them in the column are 0 or N/A?

And do you mean #n/a as in the result from =na() or do you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column from
columnS to columnAD, also if column is empty or columns
is "0" or column is "NA: need to delete as well. how to
write the macro for that.

2).I have another worksheet need to delete the columnR to
columnEE, except columnS and columnEF, how to write the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

Dave:

Which Macro which I use to delete is Macro1() or Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE, but not
column S column, then delete EF column, I know EF column
is outside of range?

#1). I need to deleted the column if is empty, also any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----
the easy one first.

#2. Record a macro when you do it manually. I selected
R, and T:EE, then
rightclick|Delete and got something that looked like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column To .Range
("S1").Column Step -1
If Application.CountA(.Columns(myCol)) = 0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union(delRng, .Cells(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff. Are
you checking one cell,
all cells or if any of them in the column are 0 or N/A?

And do you mean #n/a as in the result from =na() or do
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column from
columnS to columnAD, also if column is empty or columns
is "0" or column is "NA: need to delete as well. how to
write the macro for that.

2).I have another worksheet need to delete the columnR
to
columnEE, except columnS and columnEF, how to write the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

.

Lillian[_5_]

Dave:

It's me again, I have use your old testme02() macro,
try to concatenate from columnI to columnP, I use your
macro testme02(), but I got out of range, here is my code:

Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "i").End(xlUp).Row
.Columns("I:I").Insert
With .Range("I1:I" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5] & "" "" &
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("I:Q").EntireColumn.Hidden = True
'.Range("I:Q").EntireColumn.Delete
End With
End Sub

what's wrong with this code, on the columnQ was not empty
column, has some data, is this code will work on this?

thanks.

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

Which Macro which I use to delete is Macro1() or Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE, but not
column S column, then delete EF column, I know EF column
is outside of range?

#1). I need to deleted the column if is empty, also any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----
the easy one first.

#2. Record a macro when you do it manually. I
selected
R, and T:EE, then
rightclick|Delete and got something that looked like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column To .Range
("S1").Column Step -1
If Application.CountA(.Columns(myCol)) = 0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union(delRng, .Cells
(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff. Are
you checking one cell,
all cells or if any of them in the column are 0 or N/A?

And do you mean #n/a as in the result from =na() or do
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your
knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column from
columnS to columnAD, also if column is empty or
columns
is "0" or column is "NA: need to delete as well. how
to
write the macro for that.

2).I have another worksheet need to delete the
columnR
to
columnEE, except columnS and columnEF, how to write
the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

.

.

Dave:

When I use the following code:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

This only delete from columnR to columnED inside of data,
but the column R to ED still there, it's become empty
column, is anyway can delete them all?

thanks.

Lillian

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

It's me again, I have use your old testme02() macro,
try to concatenate from columnI to columnP, I use your
macro testme02(), but I got out of range, here is my
code:

Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "i").End(xlUp).Row
.Columns("I:I").Insert
With .Range("I1:I" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5] & "" ""
&
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("I:Q").EntireColumn.Hidden = True
'.Range("I:Q").EntireColumn.Delete
End With
End Sub

what's wrong with this code, on the columnQ was not
empty
column, has some data, is this code will work on this?

thanks.

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

Which Macro which I use to delete is Macro1() or
Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE, but not
column S column, then delete EF column, I know EF
column
is outside of range?

#1). I need to deleted the column if is empty, also any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----
the easy one first.

#2. Record a macro when you do it manually. I
selected
R, and T:EE, then
rightclick|Delete and got something that looked like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column To .Range
("S1").Column Step -1
If Application.CountA(.Columns(myCol)) = 0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union(delRng, .Cells
(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff. Are
you checking one cell,
all cells or if any of them in the column are 0 or N/A?

And do you mean #n/a as in the result from =na() or do
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your
knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column
from
columnS to columnAD, also if column is empty or
columns
is "0" or column is "NA: need to delete as well. how
to
write the macro for that.

2).I have another worksheet need to delete the
columnR
to
columnEE, except columnS and columnEF, how to write
the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

.

.

.

Dave Peterson[_3_]

Excel always has 256 columns (A:IV).

If you delete one, then the others shift over and a new one appears at the far
right. You really can't make them go away completely.

But if you just don't like to see them, you could hide them instead of deleting
them.

Sub Macro2A()
With Activesheet
.range("R:R,T:EE").entirecolumn.hidden = true
end with
End Sub

wrote:

Dave:

When I use the following code:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

This only delete from columnR to columnED inside of data,
but the column R to ED still there, it's become empty
column, is anyway can delete them all?

thanks.

Lillian

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

It's me again, I have use your old testme02() macro,
try to concatenate from columnI to columnP, I use your
macro testme02(), but I got out of range, here is my
code:

Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "i").End(xlUp).Row
.Columns("I:I").Insert
With .Range("I1:I" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5] & "" ""
&
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("I:Q").EntireColumn.Hidden = True
'.Range("I:Q").EntireColumn.Delete
End With
End Sub

what's wrong with this code, on the columnQ was not
empty
column, has some data, is this code will work on this?

thanks.

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

Which Macro which I use to delete is Macro1() or
Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE, but not
column S column, then delete EF column, I know EF
column
is outside of range?

#1). I need to deleted the column if is empty, also any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----
the easy one first.

#2. Record a macro when you do it manually. I
selected
R, and T:EE, then
rightclick|Delete and got something that looked like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column To .Range
("S1").Column Step -1
If Application.CountA(.Columns(myCol)) = 0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union(delRng, .Cells
(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff. Are
you checking one cell,
all cells or if any of them in the column are 0 or N/A?

And do you mean #n/a as in the result from =na() or do\0
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your
knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column
from
columnS to columnAD, also if column is empty or
columns
is "0" or column is "NA: need to delete as well. how
to
write the macro for that.

2).I have another worksheet need to delete the
columnR
to
columnEE, except columnS and columnEF, how to write
the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

.

.

.

--

Dave Peterson

Dave Peterson[_3_]

#2. I'd use macro1A. But see my other post first.

#1. I'm still confused about #1. What does any column equal to 0 mean?

Does the whole column have to sum to 0?
like:

3
5
-7
1
-2

Or does it mean that row 1 (or some other row) equals 0?

And same with NA. Do you mean if any cell has NA in it, it should be deleted?
Or if ALL the cells in the column have NA. (Either way, does NA mean you typed
in NA or it was the result of a formula like =vlookup(a1,sheet3!$a$1:$c$999,3,
false) that returns #n/a if no match was found?

wrote:

Dave:

Which Macro which I use to delete is Macro1() or Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE, but not
column S column, then delete EF column, I know EF column
is outside of range?

#1). I need to deleted the column if is empty, also any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----
the easy one first.

#2. Record a macro when you do it manually. I selected
R, and T:EE, then
rightclick|Delete and got something that looked like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column To .Range
("S1").Column Step -1
If Application.CountA(.Columns(myCol)) = 0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union(delRng, .Cells(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff. Are
you checking one cell,
all cells or if any of them in the column are 0 or N/A?

And do you mean #n/a as in the result from =na() or do
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column from
columnS to columnAD, also if column is empty or columns
is "0" or column is "NA: need to delete as well. how to
write the macro for that.

2).I have another worksheet need to delete the columnR
to
columnEE, except columnS and columnEF, how to write the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

.

--

Dave Peterson

Lillian[_5_]

Dave:

Remember you asked me the question regarding delete "0"
and "NA" in the column field.
the question I have:

1). If any column has "0" or "NA" how to write the macro?
example: on the ColumnK has nothing but 0, columnG has
nothing but "NA", how to write the macro to search those
information?

thanks.

Lillian

-----Original Message-----
Excel always has 256 columns (A:IV).

If you delete one, then the others shift over and a new
one appears at the far
right. You really can't make them go away completely.

But if you just don't like to see them, you could hide
them instead of deleting
them.

Sub Macro2A()
With Activesheet
.range("R:R,T:EE").entirecolumn.hidden = true
end with
End Sub

wrote:

Dave:

When I use the following code:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

This only delete from columnR to columnED inside of
data,
but the column R to ED still there, it's become empty
column, is anyway can delete them all?

thanks.

Lillian

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

It's me again, I have use your old testme02() macro,
try to concatenate from columnI to columnP, I use your
macro testme02(), but I got out of range, here is my
code:

Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "i").End
(xlUp).Row
.Columns("I:I").Insert
With .Range("I1:I" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5] & "" ""
&
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("I:Q").EntireColumn.Hidden = True
'.Range("I:Q").EntireColumn.Delete
End With
End Sub

what's wrong with this code, on the columnQ was not
empty
column, has some data, is this code will work on this?

thanks.

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

Which Macro which I use to delete is Macro1() or
Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE, but
not
column S column, then delete EF column, I know EF
column
is outside of range?

#1). I need to deleted the column if is empty, also
any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----
the easy one first.

#2. Record a macro when you do it manually. I
selected
R, and T:EE, then
rightclick|Delete and got something that looked like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column To .Range
("S1").Column Step -1
If Application.CountA(.Columns(myCol)) =
0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union(delRng, .Cells\0
(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff.
Are
you checking one cell,
all cells or if any of them in the column are 0 or
N/A?

And do you mean #n/a as in the result from =na() or
do
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your
knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column
from
columnS to columnAD, also if column is empty or
columns
is "0" or column is "NA: need to delete as well.
how
to
write the macro for that.

2).I have another worksheet need to delete the
columnR
to
columnEE, except columnS and columnEF, how to write
the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

.

.

.

--

Dave Peterson

.

Dave Peterson[_3_]

I don't think that this is what you want, but I'm still confused:

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) = myCol.Cells.Count Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1), delRng)
End If
End If
Next myCol
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If

End Sub

This will delete the column if each of the cells in that column (within the
usedrange of the sheet) contain 0.

To get rid of the columns that contain ALL "NA" (not #n/a), change this line:
If Application.CountIf(myCol, 0) = myCol.Cells.Count Then
to
If Application.CountIf(myCol, "NA") = myCol.Cells.Count Then

Lillian wrote:

Dave:

Remember you asked me the question regarding delete "0"
and "NA" in the column field.
the question I have:

1). If any column has "0" or "NA" how to write the macro?
example: on the ColumnK has nothing but 0, columnG has
nothing but "NA", how to write the macro to search those
information?

thanks.

Lillian

-----Original Message-----
Excel always has 256 columns (A:IV).

If you delete one, then the others shift over and a new
one appears at the far
right. You really can't make them go away completely.

But if you just don't like to see them, you could hide
them instead of deleting
them.

Sub Macro2A()
With Activesheet
.range("R:R,T:EE").entirecolumn.hidden = true
end with
End Sub

wrote:

Dave:

When I use the following code:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

This only delete from columnR to columnED inside of
data,
but the column R to ED still there, it's become empty
column, is anyway can delete them all?

thanks.

Lillian

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

It's me again, I have use your old testme02() macro,
try to concatenate from columnI to columnP, I use your
macro testme02(), but I got out of range, here is my
code:

Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "i").End
(xlUp).Row
.Columns("I:I").Insert
With .Range("I1:I" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5] & "" ""
&
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("I:Q").EntireColumn.Hidden = True
'.Range("I:Q").EntireColumn.Delete
End With
End Sub

what's wrong with this code, on the columnQ was not
empty
column, has some data, is this code will work on this?

thanks.

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

Which Macro which I use to delete is Macro1() or
Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE, but
not
column S column, then delete EF column, I know EF
column
is outside of range?

#1). I need to deleted the column if is empty, also
any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----
the easy one first.\0

#2. Record a macro when you do it manually. I
selected
R, and T:EE, then
rightclick|Delete and got something that looked like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column To .Range
("S1").Column Step -1
If Application.CountA(.Columns(myCol)) =
0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union(delRng, .Cells
(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff.
Are
you checking one cell,
all cells or if any of them in the column are 0 or
N/A?

And do you mean #n/a as in the result from =na() or
do
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your
knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column
from
columnS to columnAD, also if column is empty or
columns
is "0" or column is "NA: need to delete as well.
how
to
write the macro for that.

2).I have another worksheet need to delete the
columnR
to
columnEE, except columnS and columnEF, how to write
the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

.

.

.

--

Dave Peterson

.

--

Dave Peterson

Dave Peterson[_3_]

I read your other post and this'll get both 0's and NA's.

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) = myCol.Cells.Count _
Or Application.CountIf(myCol, "na") = myCol.Cells.Count Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1), delRng)
End If
End If
Next myCol
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If

End Sub

<<snipped

--

Dave Peterson

Tom Ogilvy

And this will get the blank columns and will speed things up minimally by
not having to count the number of cells in a column over and over. Note
that if you have several blank columns at the left most (such as A and B)
which are not in the UsedRange, these will be left untouched. This is one
reason I used a different approach, but it is an unlikely scenario.

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet
Dim cnt as Long

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
cnt = myRng.columns(1).Cells.count
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) = cnt _
Or Application.CountIf(myCol, "na") = cnt _
Or Application.CountA(myCol) = 0 Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1), delRng)
End If
End If
Next myCol
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End Sub

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
I read your other post and this'll get both 0's and NA's.

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) = myCol.Cells.Count _
Or Application.CountIf(myCol, "na") = myCol.Cells.Count Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1), delRng)
End If
End If
Next myCol
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If

End Sub

<<snipped

--

Dave Peterson

Lillian[_5_]

Dave:

This is cool, thank you so much.

Lillian
-----Original Message-----
I read your other post and this'll get both 0's and NA's.

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) =
myCol.Cells.Count _
Or Application.CountIf(myCol, "na") =
myCol.Cells.Count Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1),
delRng)
End If
End If
Next myCol
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If

End Sub

<<snipped

--

Dave Peterson

.

Lillian[_5_]

Tom:

This one even better, I really appreciated all your
effort.

Lillian
-----Original Message-----

And this will get the blank columns and will speed things
up minimally by
not having to count the number of cells in a column over
and over. Note
that if you have several blank columns at the left most
(such as A and B)
which are not in the UsedRange, these will be left
untouched. This is one
reason I used a different approach, but it is an unlikely
scenario.

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet
Dim cnt as Long

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
cnt = myRng.columns(1).Cells.count
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) = cnt _
Or Application.CountIf(myCol, "na") = cnt _
Or Application.CountA(myCol) = 0 Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1),
delRng)
End If
End If
Next myCol
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End Sub

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
I read your other post and this'll get both 0's and
NA's.

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) =
myCol.Cells.Count _
Or Application.CountIf(myCol, "na") =
myCol.Cells.Count Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1),
delRng)
End If
End If
Next myCol
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If

End Sub

<<snipped

--

Dave Peterson

.

Lillian[_5_]

Dave:

I try to concatenate from columnJ to columnQ, I use
your code as:

Sub ConcatFromJToQ()
Dim LastRow As Long
With Worksheets("General")
LastRow = .Cells(.Rows.Count, "j").End(xlUp).Row
.Columns("J:J").Insert
With .Range("J1:J" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5] & "" "" &
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("K:R").EntireColumn.Hidden = True
.Range("K:R").EntireColumn.Delete
End With
End Sub

and I got entire J column has Rc1...RC8, did I do anthing
wrong?
please advise.

Lillian
-----Original Message-----
Excel always has 256 columns (A:IV).

If you delete one, then the others shift over and a new
one appears at the far
right. You really can't make them go away completely.

But if you just don't like to see them, you could hide
them instead of deleting
them.

Sub Macro2A()
With Activesheet
.range("R:R,T:EE").entirecolumn.hidden = true
end with
End Sub

wrote:

Dave:

When I use the following code:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

This only delete from columnR to columnED inside of
data,
but the column R to ED still there, it's become empty
column, is anyway can delete them all?

thanks.

Lillian

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

It's me again, I have use your old testme02()
macro,
try to concatenate from columnI to columnP, I use your
macro testme02(), but I got out of range, here is my
code:

Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "i").End
(xlUp).Row
.Columns("I:I").Insert
With .Range("I1:I" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5]
& "" ""
&
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("I:Q").EntireColumn.Hidden = True
'.Range("I:Q").EntireColumn.Delete
End With
End Sub

what's wrong with this code, on the columnQ was not
empty
column, has some data, is this code will work on this?

thanks.

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

Which Macro which I use to delete is Macro1() or
Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE, but
not
column S column, then delete EF column, I know EF
column
is outside of range?

#1). I need to deleted the column if is empty, also
any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----
the easy one first.

#2. Record a macro when you do it manually. I
selected
R, and T:EE, then
rightclick|Delete and got something that looked
like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you
mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range
\0
With ActiveSheet
For myCol = .Range("AD1").Column To .Range
("S1").Column Step -1
If Application.CountA(.Columns(myCol))
= 0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union
(delRng, .Cells
(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff.
Are
you checking one cell,
all cells or if any of them in the column are 0 or
N/A?

And do you mean #n/a as in the result from =na() or
do
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your
knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column
from
columnS to columnAD, also if column is empty or
columns
is "0" or column is "NA: need to delete as well.
how
to
write the macro for that.

2).I have another worksheet need to delete the
columnR
to
columnEE, except columnS and columnEF, how to
write
the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

.

.

.

--

Dave Peterson

.

Dave Peterson[_3_]

Probably not wrong, but I could duplicate the problem if the inserted column was
formatted as Text.

So you could add a line so that you wouldn't have to worry about how the new
column J was formatted:

With .Range("J1:J" & LastRow)
.NumberFormat = "General" '<-- added
.FormulaR1C1 = "= your long formula here"
.Value = .Value
End With

I hoping that .numberformat = "general" will fix the problem.

Lillian wrote:

Dave:

I try to concatenate from columnJ to columnQ, I use
your code as:

Sub ConcatFromJToQ()
Dim LastRow As Long
With Worksheets("General")
LastRow = .Cells(.Rows.Count, "j").End(xlUp).Row
.Columns("J:J").Insert
With .Range("J1:J" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5] & "" "" &
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("K:R").EntireColumn.Hidden = True
.Range("K:R").EntireColumn.Delete
End With
End Sub

and I got entire J column has Rc1...RC8, did I do anthing
wrong?
please advise.

Lillian
-----Original Message-----
Excel always has 256 columns (A:IV).

If you delete one, then the others shift over and a new
one appears at the far
right. You really can't make them go away completely.

But if you just don't like to see them, you could hide
them instead of deleting
them.

Sub Macro2A()
With Activesheet
.range("R:R,T:EE").entirecolumn.hidden = true
end with
End Sub

wrote:

Dave:

When I use the following code:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

This only delete from columnR to columnED inside of
data,
but the column R to ED still there, it's become empty
column, is anyway can delete them all?

thanks.

Lillian

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

It's me again, I have use your old testme02()
macro,
try to concatenate from columnI to columnP, I use your
macro testme02(), but I got out of range, here is my
code:

Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "i").End
(xlUp).Row
.Columns("I:I").Insert
With .Range("I1:I" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5]
& "" ""
&
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("I:Q").EntireColumn.Hidden = True
'.Range("I:Q").EntireColumn.Delete
End With
End Sub

what's wrong with this code, on the columnQ was not
empty
column, has some data, is this code will work on this?

thanks.

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

Which Macro which I use to delete is Macro1() or
Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE, but
not
column S column, then delete EF column, I know EF
column
is outside of range?

#1). I need to deleted the column if is empty, also
any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----
the easy one first.

#2. Record a macro when you do it manually. I
selected
R, and T:EE, then
rightclick|Delete and got something that looked
like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select\0
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you
mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column To .Range
("S1").Column Step -1
If Application.CountA(.Columns(myCol))
= 0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union
(delRng, .Cells
(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff.
Are
you checking one cell,
all cells or if any of them in the column are 0 or
N/A?

And do you mean #n/a as in the result from =na() or
do
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your
knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the column
from
columnS to columnAD, also if column is empty or
columns
is "0" or column is "NA: need to delete as well.
how
to
write the macro for that.

2).I have another worksheet need to delete the
columnR
to
columnEE, except columnS and columnEF, how to
write
the
macro for this.

thanks for all the help.

Lillian

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

.

.

--

Dave Peterson

.

.

.

--

Dave Peterson

.

--

Dave Peterson

Lillian[_5_]

Dave:
This line .NumberFormat = "General is pretty helpful,
thanks.

Lillian
-----Original Message-----
Probably not wrong, but I could duplicate the problem if
the inserted column was
formatted as Text.

So you could add a line so that you wouldn't have to
worry about how the new
column J was formatted:

With .Range("J1:J" & LastRow)
.NumberFormat = "General" '<-- added
.FormulaR1C1 = "= your long formula here"
.Value = .Value
End With

I hoping that .numberformat = "general" will fix the
problem.

Lillian wrote:

Dave:

I try to concatenate from columnJ to columnQ, I use
your code as:

Sub ConcatFromJToQ()
Dim LastRow As Long
With Worksheets("General")
LastRow = .Cells(.Rows.Count, "j").End
(xlUp).Row
.Columns("J:J").Insert
With .Range("J1:J" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5]
& "" "" &
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("K:R").EntireColumn.Hidden = True
.Range("K:R").EntireColumn.Delete
End With
End Sub

and I got entire J column has Rc1...RC8, did I do
anthing
wrong?
please advise.

Lillian
-----Original Message-----
Excel always has 256 columns (A:IV).

If you delete one, then the others shift over and a
new
one appears at the far
right. You really can't make them go away completely.

But if you just don't like to see them, you could hide
them instead of deleting
them.

Sub Macro2A()
With Activesheet
.range("R:R,T:EE").entirecolumn.hidden = true
end with
End Sub

wrote:

Dave:

When I use the following code:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

This only delete from columnR to columnED inside of
data,
but the column R to ED still there, it's become
empty
column, is anyway can delete them all?

thanks.

Lillian

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

It's me again, I have use your old testme02()
macro,
try to concatenate from columnI to columnP, I use
your
macro testme02(), but I got out of range, here is
my
code:

Sub testme02()
Dim LastRow As Long
With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "i").End
(xlUp).Row
.Columns("I:I").Insert
With .Range("I1:I" & LastRow)
.FormulaR1C1 = "=RC[1] & "" "" & RC[2]
& "" "" & RC[3] & "" "" & RC[4] & "" "" & RC[5]
& "" ""
&
RC[6] & "" "" & RC[7] & "" "" &RC[8]"
.Value = .Value
End With
.Range("I:Q").EntireColumn.Hidden = True
'.Range("I:Q").EntireColumn.Delete
End With
End Sub

what's wrong with this code, on the columnQ was not
empty
column, has some data, is this code will work on
this?

thanks.

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

Which Macro which I use to delete is Macro1() or
Macro1A?
the question I have was:
#2). I need to deleted any column from R to EE,
but
not
column S column, then delete EF column, I know EF
column
is outside of range?

#1). I need to deleted the column if is empty,
also
any
of column as "0" and "NA" need to delete as well.

thanks for all the help.

Lillian
-----Original Message-----\0
the easy one first.

#2. Record a macro when you do it manually. I
selected
R, and T:EE, then
rightclick|Delete and got something that looked
like:

Option Explicit
Sub Macro1()
Range("R:R,T:EE").Select
Selection.Delete Shift:=xlToLeft
End Sub

(deleting from the right to left will mean you
don't
have to adjust the column
letters after the deletion.)

But you could change it to something like:

Option Explicit
Sub Macro1A()
With Activesheet
.range("R:R,T:EE").entirecolumn.delete
end with
End Sub

And column EF was outside of the range. Did you
mean
that?

And #1.

To delete the columns that are empty:

Option Explicit
Sub testme01()

Dim myCol As Long
Dim delRng As Range

With ActiveSheet
For myCol = .Range("AD1").Column
To .Range
("S1").Column Step -1
If Application.CountA(.Columns
(myCol))
= 0
Then
If delRng Is Nothing Then
Set delRng = .Cells(1, myCol)
Else
Set delRng = Union
(delRng, .Cells
(1,
myCol))
End If
End If

Next myCol

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End With

End Sub

But I'm confused about the n/a stuff and 0 stuff.
Are
you checking one cell,
all cells or if any of them in the column are 0
or
N/A?

And do you mean #n/a as in the result from =na()
or
do
you mean the text N/A
(typed in)?

Lillian wrote:

Dave:

I have another question, since I like your
knowledge
so much, would you mind I ask you another one,

1).I have one worksheet need to delete the
column
from
columnS to columnAD, also if column is empty or
columns
is "0" or column is "NA: need to delete as
well.
how
to
write the macro for that.

2).I have another worksheet need to delete the
columnR
to
columnEE, except columnS and columnEF, how to
write
the
macro for this.

thanks for all the help.

Lillian

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 greatet. 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

.

.

--

Dave Peterson

.

.

.

--

Dave Peterson

.

--

Dave Peterson

.

Lillian[_5_]

Tom:

I did the test on this code, it only delete when column
is NA, but not column is 0, but it did delete the empty
column.
example:

A B C D E F G
0 NA 0 0 0
0 NA 0 0 0
0 NA 0 0 0

after run this macro, it become like this
A B C D
0 0 0 0
0 0 0 0
0 0 0 0

Lillian
-----Original Message-----

And this will get the blank columns and will speed
things up minimally by
not having to count the number of cells in a column over
and over. Note
that if you have several blank columns at the left most
(such as A and B)
which are not in the UsedRange, these will be left
untouched. This is one
reason I used a different approach, but it is an
unlikely scenario.

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet
Dim cnt as Long

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
cnt = myRng.columns(1).Cells.count
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) = cnt _
Or Application.CountIf(myCol, "na") = cnt _
Or Application.CountA(myCol) = 0 Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1),
delRng)
End If
End If
Next myCol
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If
End Sub

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
I read your other post and this'll get both 0's and
NA's.

Option Explicit
Sub testme03()
Dim myRng As Range
Dim delRng As Range
Dim myCol As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = .UsedRange
For Each myCol In myRng.Columns
If Application.CountIf(myCol, 0) =
myCol.Cells.Count _
Or Application.CountIf(myCol, "na") =
myCol.Cells.Count Then
If delRng Is Nothing Then
Set delRng = myCol.Cells(1)
Else
Set delRng = Union(myCol.Cells(1),
delRng)
End If
End If
Next myCol
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireColumn.Delete
End If

End Sub

<<snipped

--

Dave Peterson

.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I draw chemical structures in Excel spreed sheet?	James	Charts and Charting in Excel	1	March 31st 09 08:29 AM
save a excel spreed sheet to a word document?	allend27	Excel Discussion (Misc queries)	2	July 29th 08 12:44 PM
Formating Excel Spreed Sheet	xkaylax	Excel Discussion (Misc queries)	1	January 31st 08 01:43 AM
find last record in macro and delete all after	Sherife	Excel Discussion (Misc queries)	3	September 18th 06 03:51 AM
How can I read over than 65,536 rows from excel spreed sheet	Lillian	Excel Discussion (Misc queries)	2	April 16th 06 01:47 AM

Menu

About Us