Deleting rows using a variable

  • Thread starter Thread starter Kirk A
  • Start date Start date
K

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