Code to delete entire row

[erock24]

I've got a wierd one here for you guys.
I need help in selecting a range and deleting an entire row if A in my range is not empty. I want to only keep rows if A is empty.
The trick is my range has to be from this row.

Range("F3").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select

What ever cell that lands on I need the range to be from that row in A to the end of I. So, if that cell is F45 and column I goes to 75. I need my range to be A45:A75. Then, anything in that range that is not empty delete entire row keeping rows whose cells in A are empty.
I hope this makes sense.
Thanks for your time and help.

 

[VoG]

Try

Sub test()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = LastRow To 1 Step -1
    If Not IsEmpty(Cells(i, 1).Value) Then Cells(i, 1).EntireRow.Delete
Next i
End Sub

Please note that it is not necessary to select cells to work with them.

 

[erock24]

That did the delete on the entire rows if A was not empty, but I did it in places I didn't want it to. My range is very specific and I don't know how else to describe it. Except if you go to the last cell used in I and do ctrl+ up arrow or go to F3 and do ctrl + down arrow 2x. that is the row. Delete everything from there to the end of I if A is not empty.

 

[VoG]

Then

Sub test()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, 9).End(xlUp).Row
For i = LastRow To 1 Step -1
    If Not IsEmpty(Cells(i, 1).Value) Then Cells(i, 1).EntireRow.Delete
Next i
End Sub

 

[erock24]

I see what you did as far as going to the end of I and doing the xlup, but for some reason it is still deleting everything. When I go to the last used row in I and hit ctrl + uparrow I land right where I need to be not at the top of the page. I don't know why your code is not working.

 

[gardnertoo]

I haven't figured out how to FIX it, but I have figured out what's wrong. The "for - next" loop as written goes from Lastrow all the way up the page to row 1. There needs to be a new variable, Firstrow, in place of the "1" in the "for-next" loop.

 

[gardnertoo]

I think I've got it. Try this:

Sub test()
Dim LastRow As Long, FirstRow As Long, i As Long, message As String

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
FirstRow = Cells(Cells(3, 6).End(xlDown).Row, 6).End(xlDown).Row  'xlDown twice from cell F3
'MsgBox (LastRow)
'MsgBox (FirstRow)

For i = LastRow To FirstRow Step -1
    If Not IsEmpty(Cells(i, 1).Value) Then Cells(i, 1).EntireRow.Delete
    
Next i
End Sub

If I've correctly understood what you're trying to accomplish with this macro, this should get it done.

 

[erock24]

Wow!! very cool. That works perfectly. Absolutely spot on.