Delete Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
'<<================
 
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
 
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:

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
 
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
 
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.
 
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
 
Back
Top