preset range
 

I have the following Module:
Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = 0 Then 'You can replace "" with 0 to delete rows
with 'the value zero
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Company" Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Controlling Area :" Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Proj. number" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub

What can I add so I don't have to select the data and use a pre determined
range of "A8:Z300" as the selection set. Also is there a way to make the code
smaller?

preset range
 

This is one alternative:

Sub DelEmptyRow()
Dim Rng As Range, sRng As Range, i As Long, col As Long
col = ActiveCell.Column
Set Rng = Range(ActiveCell.Address & ":" & _
ActiveSheet.Cells(Rows.Count, col). _
End(xlUp).Address)
For i = Rng.Rows.Count To 1 Step -1
Set sRng = ActiveSheet.Cells(i, col)
If sRng = "Company" Or sRng = "Controlling Area :" Or _
sRng = "Proj. number" Then
sRng.EntireRow.Delete
End If
Next
End Sub


"Novice Lee" wrote:

I have the following Module:
Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = 0 Then 'You can replace "" with 0 to delete rows
with 'the value zero
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Company" Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Controlling Area :" Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Proj. number" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub

What can I add so I don't have to select the data and use a pre determined
range of "A8:Z300" as the selection set. Also is there a way to make the code
smaller?

preset range
 

Are those three text strings you are testing for all located in the same
column (if so, which one) or can the be located in any of the columns within
the range A8:Z300?

--
Rick (MVP - Excel)


"Novice Lee" wrote in message
...
I have the following Module:
Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = 0 Then 'You can replace "" with 0 to delete rows
with 'the value zero
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Company" Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Controlling Area :" Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Proj. number" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub

What can I add so I don't have to select the data and use a pre determined
range of "A8:Z300" as the selection set. Also is there a way to make the
code
smaller?

preset range
 

Rick

Yes they are all in column a

"Rick Rothstein" wrote:

Are those three text strings you are testing for all located in the same
column (if so, which one) or can the be located in any of the columns within
the range A8:Z300?

--
Rick (MVP - Excel)


"Novice Lee" wrote in message
...
I have the following Module:
Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = 0 Then 'You can replace "" with 0 to delete rows
with 'the value zero
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Company" Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Controlling Area :" Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Value = "Proj. number" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub

What can I add so I don't have to select the data and use a pre determined
range of "A8:Z300" as the selection set. Also is there a way to make the
code
smaller?