Delete Unwanted Rows (Help with Range)

[Santa-D]

I've got the following function but unfortunately I'm not too sure how
to modify the range to go down to the bottom of the Spreadsheet then
delete rows, so I did it manually, because my journals are getting
larger and larger the range isn't good enough and I want to set it
automatically. Can someone assist me in how to get there?

---------------------------------------------------------------------------------------------------------------------------------------
Sub Delete_Unwanted_Rows_2()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual




Dim Rng As Range
Dim i As Integer, counter As Integer


'Set the range to evaluate to rng.
Set Rng = Range("B18:B1147")

'initialize i to 1
i = 1

'Loop for a count of 1 to the number of rows
'in the range that you want to evaluate.
For counter = 1 To Rng.Rows.Count

'If cell i in the range contains an "x",
'delete the row.
'Else increment i
If Rng.Cells(i) = "" Then
Rng.Cells(i).EntireRow.Delete
Else
i = i + 1
End If

Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
---------------------------------------------------------------------------------------------------------------------------------------


The last row which is the balance has an "O" in column B. I think the
..xldown should be used but I'm not too sure how to use it.
Thank you for your help.

 

[Norman Jones]

Hi Santa-D,

Try:

Dim iLrow As Long

iLrow = Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range("B18:B" & iLrow)