Code to delete entire row

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,163
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.
Code:
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try

Code:
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.
 
Upvote 0
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.
 
Upvote 0
Then

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
I think I've got it. Try this:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top