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)


---
Regards,
Norman



"Santa-D" <[email protected]> wrote in message
news:[email protected]...
> 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.
>