delete rows
Hi,
I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i,"B") Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1,Rows(i)) End If end If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then ..Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
This one will work too...
Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
I have tried both of the methods that you have suggested but unfortuantely no
one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
A couple questions for you:
1) Are the cells you are trying use to delete the rows actually the "#N/A" error, or do you want to delete all errors. 2) What kind of values were in the cells that were deleted that weren't suppose to be removed Sandy massi wrote: I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then ..Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
I agree with you Bob, yours code is def the one to go with...
Sandy Bob Phillips wrote: Public Sub Tester02() Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
I couldn't reply earlier as I had the i-net down. At the end I manage to find
a solution.: '------------------------------------------------------------------------------------------- ' this part transform the links in normal test and substitute #N/A with blank cel '------------------------------------------------------------------------------------------- Cells.Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub '-------------------------------------------------------- 'and this one remove all the blank cells '-------------------------------------------------------- Sub Macro02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b1:b10000") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub it might not be a masterpiece of VB but it works fine. It was the first time I putting my hands on a macro... now I need to apply this macro to different files. What I have done at the moment is: I have a blank excel file with the macro recorded. Then I open the files I need to work on and I run the macro from tools/macro. Is there a finer way to make this macro working for the files? Other people should work with this file and I was hoping to find a better solution. cheers Thanks for your help "Sandy" wrote: I agree with you Bob, yours code is def the one to go with... Sandy Bob Phillips wrote: Public Sub Tester02() Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
Look at creating an addin.
http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I couldn't reply earlier as I had the i-net down. At the end I manage to find a solution.: '--------------------------------------------------------------------------- ---------------- ' this part transform the links in normal test and substitute #N/A with blank cell '--------------------------------------------------------------------------- ---------------- Cells.Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub '-------------------------------------------------------- 'and this one remove all the blank cells '-------------------------------------------------------- Sub Macro02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b1:b10000") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub it might not be a masterpiece of VB but it works fine. It was the first time I putting my hands on a macro... now I need to apply this macro to different files. What I have done at the moment is: I have a blank excel file with the macro recorded. Then I open the files I need to work on and I run the macro from tools/macro. Is there a finer way to make this macro working for the files? Other people should work with this file and I was hoping to find a better solution. cheers Thanks for your help "Sandy" wrote: I agree with you Bob, yours code is def the one to go with... Sandy Bob Phillips wrote: Public Sub Tester02() Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
Hi Bob,
good afternoon, I have creeated the addin no problem but how do i use it? sorry but this is all new for me... Massimo "Bob Phillips" wrote: Look at creating an addin. http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I couldn't reply earlier as I had the i-net down. At the end I manage to find a solution.: '--------------------------------------------------------------------------- ---------------- ' this part transform the links in normal test and substitute #N/A with blank cell '--------------------------------------------------------------------------- ---------------- Cells.Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub '-------------------------------------------------------- 'and this one remove all the blank cells '-------------------------------------------------------- Sub Macro02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b1:b10000") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub it might not be a masterpiece of VB but it works fine. It was the first time I putting my hands on a macro... now I need to apply this macro to different files. What I have done at the moment is: I have a blank excel file with the macro recorded. Then I open the files I need to work on and I run the macro from tools/macro. Is there a finer way to make this macro working for the files? Other people should work with this file and I was hoping to find a better solution. cheers Thanks for your help "Sandy" wrote: I agree with you Bob, yours code is def the one to go with... Sandy Bob Phillips wrote: Public Sub Tester02() Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
You then distribute the addin to the other desktops, and they install it,
ToolsAddinsBrowse -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi Bob, good afternoon, I have creeated the addin no problem but how do i use it? sorry but this is all new for me... Massimo "Bob Phillips" wrote: Look at creating an addin. http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I couldn't reply earlier as I had the i-net down. At the end I manage to find a solution.: '--------------------------------------------------------------------------- ---------------- ' this part transform the links in normal test and substitute #N/A with blank cell '--------------------------------------------------------------------------- ---------------- Cells.Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub '-------------------------------------------------------- 'and this one remove all the blank cells '-------------------------------------------------------- Sub Macro02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b1:b10000") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub it might not be a masterpiece of VB but it works fine. It was the first time I putting my hands on a macro... now I need to apply this macro to different files. What I have done at the moment is: I have a blank excel file with the macro recorded. Then I open the files I need to work on and I run the macro from tools/macro. Is there a finer way to make this macro working for the files? Other people should work with this file and I was hoping to find a better solution. cheers Thanks for your help "Sandy" wrote: I agree with you Bob, yours code is def the one to go with... Sandy Bob Phillips wrote: Public Sub Tester02() Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
installation is ok. I have it now in the addins list available but i don't
know how to use it. should i have a short cut in the menu or something? what i want is: a have a few files where i want to run my macro. at the moment i created an empty file with the macro and i keep it open. the i open the other files when needed and i run the macro from the 1st file. can i make the addin work like this? "Bob Phillips" wrote: You then distribute the addin to the other desktops, and they install it, ToolsAddinsBrowse -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi Bob, good afternoon, I have creeated the addin no problem but how do i use it? sorry but this is all new for me... Massimo "Bob Phillips" wrote: Look at creating an addin. http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I couldn't reply earlier as I had the i-net down. At the end I manage to find a solution.: '--------------------------------------------------------------------------- ---------------- ' this part transform the links in normal test and substitute #N/A with blank cell '--------------------------------------------------------------------------- ---------------- Cells.Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub '-------------------------------------------------------- 'and this one remove all the blank cells '-------------------------------------------------------- Sub Macro02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b1:b10000") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub it might not be a masterpiece of VB but it works fine. It was the first time I putting my hands on a macro... now I need to apply this macro to different files. What I have done at the moment is: I have a blank excel file with the macro recorded. Then I open the files I need to work on and I run the macro from tools/macro. Is there a finer way to make this macro working for the files? Other people should work with this file and I was hoping to find a better solution. cheers Thanks for your help "Sandy" wrote: I agree with you Bob, yours code is def the one to go with... Sandy Bob Phillips wrote: Public Sub Tester02() Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
I would add a button to the menu, like this
'If you put code in the appropriate workbook open event, and 'delete it in the close it will exist only for that workbook. 'Here is an example of a building a commandbar on the fly 'when you open a workbook. It adds a sub-menu to the Tools menu. Option Explicit Private Sub Workbook_Open() Dim oCb As CommandBar Dim oCtl As CommandBarPopup Dim oCtlBtn As CommandBarButton On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("myMacroButton").Delete On Erroro GoTo 0 Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtlBtn = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtlBtn.Caption = "myMacroButton" oCtlBtn.FaceId = 161 oCtlBtn.OnAction = "myMacro" End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCb As CommandBar Set oCb = Application.CommandBars("Worksheet Menu Bar") oCb.Controls("myMacroButton").Delete End Sub 'To add this, go to the VB IDE (ALT-F11 from Excel), and in 'the explorer pane, select your workbook. Then select the 'ThisWorkbook object (it's in Microsoft Excel Objects which 'might need expanding). Double-click the ThisWorkbook and 'a code window will open up. Copy this code into there, 'changing the caption and action to suit. 'This is part of the workbook, and will only exist with the 'workbook, but will be available to anyone who opens the 'workbook. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... installation is ok. I have it now in the addins list available but i don't know how to use it. should i have a short cut in the menu or something? what i want is: a have a few files where i want to run my macro. at the moment i created an empty file with the macro and i keep it open. the i open the other files when needed and i run the macro from the 1st file. can i make the addin work like this? "Bob Phillips" wrote: You then distribute the addin to the other desktops, and they install it, ToolsAddinsBrowse -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi Bob, good afternoon, I have creeated the addin no problem but how do i use it? sorry but this is all new for me... Massimo "Bob Phillips" wrote: Look at creating an addin. http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I couldn't reply earlier as I had the i-net down. At the end I manage to find a solution.: '--------------------------------------------------------------------------- ---------------- ' this part transform the links in normal test and substitute #N/A with blank cell '--------------------------------------------------------------------------- ---------------- Cells.Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub '-------------------------------------------------------- 'and this one remove all the blank cells '-------------------------------------------------------- Sub Macro02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b1:b10000") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub it might not be a masterpiece of VB but it works fine. It was the first time I putting my hands on a macro... now I need to apply this macro to different files. What I have done at the moment is: I have a blank excel file with the macro recorded. Then I open the files I need to work on and I run the macro from tools/macro. Is there a finer way to make this macro working for the files? Other people should work with this file and I was hoping to find a better solution. cheers Thanks for your help "Sandy" wrote: I agree with you Bob, yours code is def the one to go with... Sandy Bob Phillips wrote: Public Sub Tester02() Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub '<<======================= I have tried this one below but it doesn't work. '======================= Public Sub Delete_empty() If IsError(.Cells(Lrow, "b").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "b").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "ron" in Column A, case sensitive. End If End Sub '<<======================= any suggestion? thankx |
delete rows
thanks
Rgds Massi "Bob Phillips" wrote: I would add a button to the menu, like this 'If you put code in the appropriate workbook open event, and 'delete it in the close it will exist only for that workbook. 'Here is an example of a building a commandbar on the fly 'when you open a workbook. It adds a sub-menu to the Tools menu. Option Explicit Private Sub Workbook_Open() Dim oCb As CommandBar Dim oCtl As CommandBarPopup Dim oCtlBtn As CommandBarButton On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("myMacroButton").Delete On Erroro GoTo 0 Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtlBtn = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtlBtn.Caption = "myMacroButton" oCtlBtn.FaceId = 161 oCtlBtn.OnAction = "myMacro" End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCb As CommandBar Set oCb = Application.CommandBars("Worksheet Menu Bar") oCb.Controls("myMacroButton").Delete End Sub 'To add this, go to the VB IDE (ALT-F11 from Excel), and in 'the explorer pane, select your workbook. Then select the 'ThisWorkbook object (it's in Microsoft Excel Objects which 'might need expanding). Double-click the ThisWorkbook and 'a code window will open up. Copy this code into there, 'changing the caption and action to suit. 'This is part of the workbook, and will only exist with the 'workbook, but will be available to anyone who opens the 'workbook. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... installation is ok. I have it now in the addins list available but i don't know how to use it. should i have a short cut in the menu or something? what i want is: a have a few files where i want to run my macro. at the moment i created an empty file with the macro and i keep it open. the i open the other files when needed and i run the macro from the 1st file. can i make the addin work like this? "Bob Phillips" wrote: You then distribute the addin to the other desktops, and they install it, ToolsAddinsBrowse -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi Bob, good afternoon, I have creeated the addin no problem but how do i use it? sorry but this is all new for me... Massimo "Bob Phillips" wrote: Look at creating an addin. http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I couldn't reply earlier as I had the i-net down. At the end I manage to find a solution.: '--------------------------------------------------------------------------- ---------------- ' this part transform the links in normal test and substitute #N/A with blank cell '--------------------------------------------------------------------------- ---------------- Cells.Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub '-------------------------------------------------------- 'and this one remove all the blank cells '-------------------------------------------------------- Sub Macro02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b1:b10000") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub it might not be a masterpiece of VB but it works fine. It was the first time I putting my hands on a macro... now I need to apply this macro to different files. What I have done at the moment is: I have a blank excel file with the macro recorded. Then I open the files I need to work on and I run the macro from tools/macro. Is there a finer way to make this macro working for the files? Other people should work with this file and I was hoping to find a better solution. cheers Thanks for your help "Sandy" wrote: I agree with you Bob, yours code is def the one to go with... Sandy Bob Phillips wrote: Public Sub Tester02() Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 |
delete rows
"Bob Phillips" wrote: I would add a button to the menu, like this 'If you put code in the appropriate workbook open event, and 'delete it in the close it will exist only for that workbook. 'Here is an example of a building a commandbar on the fly 'when you open a workbook. It adds a sub-menu to the Tools menu. Option Explicit Private Sub Workbook_Open() Dim oCb As CommandBar Dim oCtl As CommandBarPopup Dim oCtlBtn As CommandBarButton On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("myMacroButton").Delete On Erroro GoTo 0 Set oCb = Application.CommandBars("Worksheet Menu Bar") With oCb Set oCtlBtn = .Controls.Add( _ Type:=msoControlButton, _ temporary:=True) oCtlBtn.Caption = "myMacroButton" oCtlBtn.FaceId = 161 oCtlBtn.OnAction = "myMacro" End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim oCb As CommandBar Set oCb = Application.CommandBars("Worksheet Menu Bar") oCb.Controls("myMacroButton").Delete End Sub 'To add this, go to the VB IDE (ALT-F11 from Excel), and in 'the explorer pane, select your workbook. Then select the 'ThisWorkbook object (it's in Microsoft Excel Objects which 'might need expanding). Double-click the ThisWorkbook and 'a code window will open up. Copy this code into there, 'changing the caption and action to suit. 'This is part of the workbook, and will only exist with the 'workbook, but will be available to anyone who opens the 'workbook. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... installation is ok. I have it now in the addins list available but i don't know how to use it. should i have a short cut in the menu or something? what i want is: a have a few files where i want to run my macro. at the moment i created an empty file with the macro and i keep it open. the i open the other files when needed and i run the macro from the 1st file. can i make the addin work like this? "Bob Phillips" wrote: You then distribute the addin to the other desktops, and they install it, ToolsAddinsBrowse -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi Bob, good afternoon, I have creeated the addin no problem but how do i use it? sorry but this is all new for me... Massimo "Bob Phillips" wrote: Look at creating an addin. http://support.microsoft.com/default.aspx?kbid=211563 How to create an add-in file in Excel 2000 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I couldn't reply earlier as I had the i-net down. At the end I manage to find a solution.: '--------------------------------------------------------------------------- ---------------- ' this part transform the links in normal test and substitute #N/A with blank cell '--------------------------------------------------------------------------- ---------------- Cells.Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub '-------------------------------------------------------- 'and this one remove all the blank cells '-------------------------------------------------------- Sub Macro02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b1:b10000") '<<====== CHANGE to suit On Error Resume Next Set Rng1 = Intersect(Rng, _ Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub it might not be a masterpiece of VB but it works fine. It was the first time I putting my hands on a macro... now I need to apply this macro to different files. What I have done at the moment is: I have a blank excel file with the macro recorded. Then I open the files I need to work on and I run the macro from tools/macro. Is there a finer way to make this macro working for the files? Other people should work with this file and I was hoping to find a better solution. cheers Thanks for your help "Sandy" wrote: I agree with you Bob, yours code is def the one to go with... Sandy Bob Phillips wrote: Public Sub Tester02() Dim Rng As Range, Rng1 As Range For i = 10000 To 7 Step -1 If IsError(Cells(i, "B")) Then If Rng1 Is Nothing Then Set Rng1 = Rows(i) Else Set Rng1 = Union(Rng1, Rows(i)) End If End If Next i If Not Rng1 Is Nothing Then Rng1.Delete End Sub I think Sandy's doesn't work because he is going top-down, and the pointers get out of sync, which is why I went bottom up. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... I have tried both of the methods that you have suggested but unfortuantely no one works. The one from Bob returns me Compile Error: Syntax error and it gets stuck while the one. and yours it works only partially, meaning that it deletes some rows but not all and it deletes also the ones with the relevant information i want to keep. any idea why? thank you "Sandy" wrote: This one will work too... Sub DeleteRow() Dim MyCell As Range On Error Resume Next For Each MyCell In Range("B1:B10000") If CVErr(MyCell) = CVErr(xlErrNA) Then MyCell.EntireRow.Delete End If DoEvents Next MyCell End Sub Sandy massi wrote: Hi, I need a macro that check the values of a column (let's say B) which has links to another sheet. in case the cell is equal to "#N/A" I want to delete the whole row. I have a macro that works fine if the cell is empty (see below) : '======================= Public Sub Tester02() Dim Rng As Range, Rng1 As Range Set Rng = Range("b7:b10000") '<<====== from b7 to b10000 |
All times are GMT +1. The time now is 04:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com