Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
Hi Again,
Can anyone help with this one? Is there a way that Row/s can be deleted depending on names in a range? For example: If a range (called NAMES) had FRED in it and rows 10,20,30 had the name FRED in column G, all those rows would be deleted. My poor attempt: Row = Range("NAMES").Text Rows(Range("NAMES").Text).Delete Again many thanks for help John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
Hi John,
Try: '================ Public Sub TesterX() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rCell As Range Dim delRng As Range Dim CalcMode As Long Const SearchCol As String = "G" '<<===== CHANGE Set WB = Workbooks("YourBook.xls") '<<===== CHANGE Set SH = WB.Sheets("Sheet4") '<<===== CHANGE With SH Set rng = Intersect(.Range("Names"), .Columns(SearchCol)) End With If rng Is Nothing Then Exit Sub On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In rng.Cells If UCase(rCell.Value) = "FRED" Then If delRng Is Nothing Then Set delRng = rCell Else Set delRng = Union(rCell, delRng) End If End If Next rCell If Not delRng Is Nothing Then delRng.EntireRow.Delete End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<================ --- Regards, Norman "JohnUK" wrote in message ... Hi Again, Can anyone help with this one? Is there a way that Row/s can be deleted depending on names in a range? For example: If a range (called NAMES) had FRED in it and rows 10,20,30 had the name FRED in column G, all those rows would be deleted. My poor attempt: Row = Range("NAMES").Text Rows(Range("NAMES").Text).Delete Again many thanks for help John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
Hi Norman,
Thanks for your help. I ran your code and instead of deleting the rows with the name I entered into the range, it deleted the range instead. Did I do something wrong John "Norman Jones" wrote: Hi John, Try: '================ Public Sub TesterX() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rCell As Range Dim delRng As Range Dim CalcMode As Long Const SearchCol As String = "G" '<<===== CHANGE Set WB = Workbooks("YourBook.xls") '<<===== CHANGE Set SH = WB.Sheets("Sheet4") '<<===== CHANGE With SH Set rng = Intersect(.Range("Names"), .Columns(SearchCol)) End With If rng Is Nothing Then Exit Sub On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In rng.Cells If UCase(rCell.Value) = "FRED" Then If delRng Is Nothing Then Set delRng = rCell Else Set delRng = Union(rCell, delRng) End If End If Next rCell If Not delRng Is Nothing Then delRng.EntireRow.Delete End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<================ --- Regards, Norman "JohnUK" wrote in message ... Hi Again, Can anyone help with this one? Is there a way that Row/s can be deleted depending on names in a range? For example: If a range (called NAMES) had FRED in it and rows 10,20,30 had the name FRED in column G, all those rows would be deleted. My poor attempt: Row = Range("NAMES").Text Rows(Range("NAMES").Text).Delete Again many thanks for help John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
Hi John,
The suggested code only looks at the Names range and it only deletes rows whose column G value is Fred. Rows which do not satify these two requirements will not be deleted. If, however, your intention is to delete the content of such rows, rather than deleting the rows themselves, try changing the line: delRng.EntireRow.Delete to delRng.EntireRow.ClearContents And if your intention is only to delete the contents of that part of the row which intersects with the Names range, then change the above line to: Intersect(delRng.EntireRow, SH.Range("Names")).ClearContents --- Regards, Norman "JohnUK" wrote in message ... Hi Norman, Thanks for your help. I ran your code and instead of deleting the rows with the name I entered into the range, it deleted the range instead. Did I do something wrong John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
Sorry Norman its me, not explaining very well.
If say I dont use a Range: If I entered a name into Cell A1 (Any name) I then want all the rows that have that name in column G Deleted. I see you have worked very hard at the code and appreciate what you have done and would fully understand if you gave up on me John "Norman Jones" wrote: Hi John, The suggested code only looks at the Names range and it only deletes rows whose column G value is Fred. Rows which do not satify these two requirements will not be deleted. If, however, your intention is to delete the content of such rows, rather than deleting the rows themselves, try changing the line: delRng.EntireRow.Delete to delRng.EntireRow.ClearContents And if your intention is only to delete the contents of that part of the row which intersects with the Names range, then change the above line to: Intersect(delRng.EntireRow, SH.Range("Names")).ClearContents --- Regards, Norman "JohnUK" wrote in message ... Hi Norman, Thanks for your help. I ran your code and instead of deleting the rows with the name I entered into the range, it deleted the range instead. Did I do something wrong John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
Hi John,
Try: '================ Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rCell As Range Dim delRng As Range Dim CalcMode As Long Dim searchStr As String Const SearchCol As String = "G" '<<===== CHANGE Set WB =Workbooks("YourBook.xls") '<<===== CHANGE Set SH = WB.Sheets("Sheet4") '<<===== CHANGE searchStr = SH.Range("A1").Value '<<===== CHANGE With SH Set rng = Intersect(.Range("Names"), .Columns(SearchCol)) End With If rng Is Nothing Then Exit Sub On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In rng.Cells rCell.Select If UCase(rCell.Value) = UCase(searchStr) Then If delRng Is Nothing Then Set delRng = rCell Else Set delRng = Union(rCell, delRng) End If End If Next rCell If Not delRng Is Nothing Then '\\ ***SEE BELOW << ====== 'Delete entire row delRng.EntireRow.Delete 'Or, delete the contents of the entire row ' delRng.EntireRow.ClearContents 'Or, delete the intersection of the row with the "Names" range ' Intersect(delRng.EntireRow, SH.Range("Names")). _ Delete Shift:=xlUp 'Or, delete contents of the intersection of the row and "Names" range ' Intersect(delRng.EntireRow, SH.Range("Names")).ClearContents End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<================ *** According to whether you wish to delete entire rows, delete part rows, delete the contents of entire rows, or delete the contents of part rows, choose the indicated code option and delete the remaining three. Note that you will additionally need to delete the initial apostrophe for the selected code line (not the preceding comment!). If there are still problems, perhaps you could additionally indicate which of these options corresponds with your requirements. --- Regards, Norman "JohnUK" wrote in message ... Sorry Norman it's me, not explaining very well. If say I don't use a Range: If I entered a name into Cell A1 (Any name) I then want all the rows that have that name in column G Deleted. I see you have worked very hard at the code and appreciate what you have done and would fully understand if you gave up on me John |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows
Hi Norman,
Superb - That worked for me!! Sorry took so long getting back to you, I had been away from the PC. Many thanks for your help - much appreciated and thank you for your patience Take care Regards John "Norman Jones" wrote: Hi John, Try: '================ Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim rCell As Range Dim delRng As Range Dim CalcMode As Long Dim searchStr As String Const SearchCol As String = "G" '<<===== CHANGE Set WB =Workbooks("YourBook.xls") '<<===== CHANGE Set SH = WB.Sheets("Sheet4") '<<===== CHANGE searchStr = SH.Range("A1").Value '<<===== CHANGE With SH Set rng = Intersect(.Range("Names"), .Columns(SearchCol)) End With If rng Is Nothing Then Exit Sub On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With For Each rCell In rng.Cells rCell.Select If UCase(rCell.Value) = UCase(searchStr) Then If delRng Is Nothing Then Set delRng = rCell Else Set delRng = Union(rCell, delRng) End If End If Next rCell If Not delRng Is Nothing Then '\\ ***SEE BELOW << ====== 'Delete entire row delRng.EntireRow.Delete 'Or, delete the contents of the entire row ' delRng.EntireRow.ClearContents 'Or, delete the intersection of the row with the "Names" range ' Intersect(delRng.EntireRow, SH.Range("Names")). _ Delete Shift:=xlUp 'Or, delete contents of the intersection of the row and "Names" range ' Intersect(delRng.EntireRow, SH.Range("Names")).ClearContents End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With End Sub '<<================ *** According to whether you wish to delete entire rows, delete part rows, delete the contents of entire rows, or delete the contents of part rows, choose the indicated code option and delete the remaining three. Note that you will additionally need to delete the initial apostrophe for the selected code line (not the preceding comment!). If there are still problems, perhaps you could additionally indicate which of these options corresponds with your requirements. --- Regards, Norman "JohnUK" wrote in message ... Sorry Norman it's me, not explaining very well. If say I don't use a Range: If I entered a name into Cell A1 (Any name) I then want all the rows that have that name in column G Deleted. I see you have worked very hard at the code and appreciate what you have done and would fully understand if you gave up on me John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Delete rows with numeric values, leave rows with text | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |