Posted to microsoft.public.excel.programming
|
|
delete record using Macro in excel spreed sheet
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
.
.
.
.
|