Deleting rows using a variable

[Kirk A]

Looking for help in trying to use a variable to allow calling the sam
sub from another in different places.

I have a project where I need to delete rows where the value of cell A
is one of the following (Abstract, Title, or Topic). I have copied th
same code 3 different times changing the value for that cell in th
sub. I know that this is inefficient.

The code is as follows:

Sub xdeleteRows()

Dim r As Long
Dim c As Range
Dim Rng As Range



On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection

Else
Set Rng = ActiveSheet.UsedRange.Rows

End If
For r = Rng.Rows.Count To 1 Step -1
If Cells(r, "A").Value = "Abstract:" Then
Rng.Rows(r).EntireRow.Delete
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


The issue is with this line: If Cells(r, "A").Value = "Abstract:" The


How can I code in a variable for the value, so that depending on wha
stage of the main Sub I am in, I can delete specific rows using
variable?

Thanks

Kir

 

[Bob Phillips]

Hi Kirk,

Her is an example

Sub DeleteAll()
xDeleteRows "Abstract"
xDeleteRows "Title"
xDeleteRows "Topic"
End Sub

Sub xdeleteRows(val As String)
Dim r As Long
Dim c As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection

Else
Set Rng = ActiveSheet.UsedRange.Rows

End If
For r = Rng.Rows.Count To 1 Step -1
If Cells(r, "A").Value = val Then
Rng.Rows(r).EntireRow.Delete
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Medemper]

Assuming your selections always start in A1, you should just have to:

Change: Sub xdeleteRows(Temp As String)

Change: If Cells(r, "A").Value = Temp Then

Call it by putting:
xdeleteRows ("abstract:")
xdeleteRows ("title:")
xdeleteRows ("topic:")

in your other code.
Looking for help in trying to use a variable to allow calling the same
sub from another in different places.

I have a project where I need to delete rows where the value of cell A1
is one of the following (Abstract, Title, or Topic). I have copied the
same code 3 different times changing the value for that cell in the
sub. I know that this is inefficient.

The code is as follows:

Sub xdeleteRows()

Dim r As Long
Dim c As Range
Dim Rng As Range



On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection

Else
Set Rng = ActiveSheet.UsedRange.Rows

End If
For r = Rng.Rows.Count To 1 Step -1
If Cells(r, "A").Value = "Abstract:" Then
Rng.Rows(r).EntireRow.Delete
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


The issue is with this line: If Cells(r, "A").Value = "Abstract:" Then


How can I code in a variable for the value, so that depending on what
stage of the main Sub I am in, I can delete specific rows using a
variable?

Thanks

Kirk

 

[William]

Hi Kirk...

Sub xdeleteRows()
Dim Rng As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange
End If
Set Rng = Application.Intersect(Rng, Columns("A:A"))
If Application.CountIf(Rng, "Abstract") + Application.CountIf(Rng, "Title")
+ Application.CountIf(Rng, "Topic") > 0 Then
Rng.Replace What:="Abstract", Replacement:="=2/0"
Rng.Replace What:="Title", Replacement:="=2/0"
Rng.Replace What:="Topic", Replacement:="=2/0"
Rng.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete Shift:=xlUp
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| Looking for help in trying to use a variable to allow calling the same
| sub from another in different places.
|
| I have a project where I need to delete rows where the value of cell A1
| is one of the following (Abstract, Title, or Topic). I have copied the
| same code 3 different times changing the value for that cell in the
| sub. I know that this is inefficient.
|
| The code is as follows:
|
| Sub xdeleteRows()
|
| Dim r As Long
| Dim c As Range
| Dim Rng As Range
|
|
|
| On Error GoTo EndMacro
| Application.ScreenUpdating = False
| Application.Calculation = xlCalculationManual
|
| If Selection.Rows.Count > 1 Then
| Set Rng = Selection
|
| Else
| Set Rng = ActiveSheet.UsedRange.Rows
|
| End If
| For r = Rng.Rows.Count To 1 Step -1
| If Cells(r, "A").Value = "Abstract:" Then
| Rng.Rows(r).EntireRow.Delete
| End If
| Next r
|
| EndMacro:
|
| Application.ScreenUpdating = True
| Application.Calculation = xlCalculationAutomatic
|
| End Sub
|
|
| The issue is with this line: If Cells(r, "A").Value = "Abstract:" Then
|
|
| How can I code in a variable for the value, so that depending on what
| stage of the main Sub I am in, I can delete specific rows using a
| variable?
|
| Thanks
|
| Kirk
|
|
| ---
| Message posted
|

 

[Kirk A]

Thanks to all. Got it working perfectly.

Kir