Auto Delete Data

[tlc53]

Hi,

I have a very large amount of data that I would like to tidy up using a macro. I've sorted the data by the relevant columns and now I have rubbish data at the end, which I would like deleted.

Column A has a Ticket Number. Once column A stops having a ticket number, any data contained under columns A:K needs to be deleted.

In this particular case (and it will change depending on the months data) Cell A11721 is the last cell with a ticket number, so (in this case) all data contained in the next 24,104 rows, so A11722 : K35825 needs to be cleared or deleted.

I don't expect my data to exceed 50,000 rows so on that basis I tried the following VBA code..

Sub DeleteBlankRows()
   Dim Cl As Range
   For Each Cl In Sheet1.Range("A2:A50000")
     Cl.EntireRow.Delete = IIf(Cl = 0, True, False)
   Next Cl
End Sub

However, it's not working and reporting error "Object Required". I'm not sure if this is the best way to do it though.

Any ideas please?

 

[Peter_SSs]

It's the 2x $2,469.95 figures on row 6 which won't go away (I've cut out the bulk of the data for this test purpose). Any idea why it might be doing that?

Yes, it's because I had to guess what you might have on your sheet and where, & I guessed incorrectly, & why I suggested some time ago.

A small set of dummy sample data using one of the methods linked from my signature block below would also be very helpful.

Make this change

<del>lrA = Range("A" & Rows.Count).End(xlUp).Row</del>
lrA = Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[tlc53]

Sorry about that!
On the plus side, it works perfect now. Thank you!!

 

[Peter_SSs]

On the plus side, it works perfect now. Thank you!!

Glad we got there in the end.