Excel VBA: remove unwanted lines based on last used range of specific column
Hello experts,
I have the following file attached.
I need to set up a procedure in order to remove lines that have empty values in column F.
The idea is the following:
1-Identify last used range related to column F
2-Remove the various lines as of last used range related to column F + 1 (to identify the first empty value of column F) till the end of sheet.
I attached dummy file with expected result in Result sheet.
Procedure should be applied in the same sheet.
If you have questions, please contact me.
Thank you for your help.
I have the following file attached.
I need to set up a procedure in order to remove lines that have empty values in column F.
The idea is the following:
1-Identify last used range related to column F
2-Remove the various lines as of last used range related to column F + 1 (to identify the first empty value of column F) till the end of sheet.
I attached dummy file with expected result in Result sheet.
Procedure should be applied in the same sheet.
If you have questions, please contact me.
Thank you for your help.
ASKER
Please try this...
Sub DeleteRowsWithEmptyColF()
Dim lr As Long
Dim rng As Range
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = xlNone
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error Resume Next
Set rng = Range("F2:F" & lr).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
Application.ScreenUpdating = True
End SubASKER
Thank you Subodh. Unable to test it right now. Just one remark, in dummy file I highlighted lines to remove to illustrate lines to remove but in real lines to removed will not be highlighted. Loop related to color reported in your code is necessary?
Thank you.
Thank you.
No, delete the line#7 which is Cells.Interior.ColorIndex = xlNone.
I think that I need to change the approach here.
Basically the code I proposed all the rows where column F is empty. That's not what you are trying to achieve. Right?
You want to check if the last row with data in column F is less than total rows of data on the sheet and if there are rows which have no data in column F, delete those rows. Right?
Basically the code I proposed all the rows where column F is empty. That's not what you are trying to achieve. Right?
You want to check if the last row with data in column F is less than total rows of data on the sheet and if there are rows which have no data in column F, delete those rows. Right?
Here is another version of code which should work if the above assumption is correct...
Sub DeleteRowsWithEmptyColF()
Dim lr As Long, flr As Long
Dim rng As Range
Application.ScreenUpdating = False
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
flr = Cells(Rows.Count, "F").End(xlUp).Row
If flr = lr Then Exit Sub
Rows(flr + 1 & ":" & lr).Delete
Application.ScreenUpdating = True
End SubASKER
Exactly. The aim is to get ride of lines which have no data at column F so if we check if last used range of activesheet with last used range related to column F we can identify them.
Another approach can be to loop on all lines and there is no data at column F remove it.
Lines to removed are always at the end after last used range of column F.
Another approach can be to loop on all lines and there is no data at column F remove it.
Lines to removed are always at the end after last used range of column F.
In that case the later solution will work, go with that.
ASKER
Thank you. I will test it and keep you informed.
ASKER
Subodh,
I tested the procedure and it works when I am in activesheet.
I was wondering how to use it when I am not at sheet concerned by the delete.
I tried the following without success:
Do you know how to proceed when I am not in sheet concerned by the delete?
Thank you for your help.
I tested the procedure and it works when I am in activesheet.
I was wondering how to use it when I am not at sheet concerned by the delete.
I tried the following without success:
Sub Delete_Row_With_Empty_Col_F()
Dim lr As Long, flr As Long
Dim rng As Range
Dim strWS As Worksheet
Application.ScreenUpdating = False
Set strWS = Worksheets("Sheet2")
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
flr = Cells(Rows.Count, "F").End(xlUp).Row
Application.ScreenUpdating = False
With strWS
If flr = lr Then Exit Sub
.Rows(flr + 1 & ":" & lr).Delete
End With
Application.ScreenUpdating = True
End SubDo you know how to proceed when I am not in sheet concerned by the delete?
Thank you for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tested and it works!
Thank you very much for your help!
Thank you very much for your help!
You're welcome!
You forgot to upload the file.