Delete Unwanted Rows (Help with Range)
 

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.

Delete Unwanted Rows (Help with Range)
 

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" wrote in message
oups.com...
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.