preset range


N

Novice Lee

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?
 
Ad

Advertisements

J

JLGWhiz

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
 
R

Rick Rothstein

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?
 
Ad

Advertisements

N

Novice Lee

Rick

Yes they are all in column a

Rick Rothstein said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top