Macro help

[chef]

Hi,
I have a file with around 12,000 absence records.

In column F, there are numerous records where employees have a "date left" entry.

I'd like to be able to automatically find all the records in column F that has a date left entry, then delete that row, then automatically delete column F after all date left records have been deleted.
Hope that makes sense and is this possible.

as always, help appreciated

regards

 

[VoG]

Try with a copy of your sheet

Sub test()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If IsDate(Range("F" & i).Value) Then Rows(i).Delete
Next i
Columns("F").Delete
Application.ScreenUpdating = True
End Sub

 

[chef]

Peter,
wow and thanks for such a quick response.

GENIUS !

 

[Peter_SSs]

With 12,000 rows (roughly half with dates in column F) that took about 40-50 seconds to run for me.

Assuming a heading row and that column F has dates or nothing in it, you might like to test this on a copy of your workbook.

If column F is not per my assumption, please give more details as another quick way may be possible.

Is column F the last column of data in your worksheet?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Rows()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    ActiveSheet.UsedRange.Sort Key1:=Range("F2"), _<br>        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _<br>        MatchCase:=False, Orientation:=xlTopToBottom, _<br>        DataOption1:=xlSortNormal<br>    <SPAN style="color:#00007F">With</SPAN> Columns("F")<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .SpecialCells(xlConstants, xlNumbers).EntireRow.Delete<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        .Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[chef]

Peter,
many thanks for this.

I am not back in the office until tues now and will try this first thing.

Yes, column F has either dates or nothing and last column is K, if that makes a difference.

Big thanks to you both for this help....most wonderful site for help ever and I have just bought and taken delivery of the dragons and vba/marco dvds from here so will try and up my game.

kind regards
ian

 

[Peter_SSs]

Yes, column F has either dates or nothing and last column is K, if that makes a difference.

Given that col F has dates or nothing, I think my code should do what you want pretty quickly (for the same data I tested previously, less than a tenth of a second on my machine) so the last col shouldn't matter. I was just asking about that for consideration if another method was required.