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?

 

[vcoolio]

Hello tlc,

Are you saying that all rows (from Column A to Column Whatever) below row 11721 are blank or displaying a zero(0) as the result of a formula calculation?

Cheerio,
vcoolio.

 

[Peter_SSs]

Once column A stops having a ticket number, ...

I'm assuming that means column A cells from then on are blank. If so, try this with a copy of your data.

Sub Delete_Blank_Rows_using_Col_A()
  Dim lr As Long, lrA As Long
  
  lr = Range("B:K").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  lrA = Range("A" & Rows.Count).End(xlUp).Row
  If lr > lrA Then Rows(lrA + 1).Resize(lr - lrA).Delete
End Sub

 

[tlc53]

Thank you! After column A stops having a ticket number the majority are then blank, however not all. There are some text headings that need to be deleted further down.

I tried your vba code, thank you Peter, but it didn't do anything. It didn't bounce back any errors either.

I have now gone down the route of using Advanced Filter which looks like it's going to do what I need!

Thank you for your help.

 

[Peter_SSs]

Thank you! After column A stops having a ticket number the majority are then blank, however not all.

Is the cell after the last ticket number always blank? If not how do you tell where the ticket numbers end? Do the ticket numbers have a particular format? (eg are they simply numerical and the other stuff in column A is not, or perhaps the ticket numbers all start with something like "Tkt..." and the stuff you want to get rid of does not?)
A small set of dummy sample data using one of the methods linked from my signature block below would also be very helpful.

 

[tlc53]

Yes, the cell after the last ticket number is always blank and then approximately the next 300 rows before text shows up. This will always be the case.
When I try running your code the screen doesn't flicker nor does anything change. For this reason, I don't think it's running.

 

[Peter_SSs]

For this reason, I don't think it's running.

I think it did run but in my earlier post I made an assumption ..

Once column A stops having a ticket number, ...

I'm assuming that means column A cells from then on are blank.

It turns out that assumption was incorrect, hence the code did not do what you expected.

Try this version instead. This time I am relying on
a. Your assertion that the cell after the last ticket number is empty, and
b. My own assumption that there are no blank cells in column A in among the ticket numbers.

Sub Delete_Blank_Rows_using_Col_A_v2()
  Dim lrTkt As Long, lrA As Long
  
  lrTkt = Range("A1").End(xlDown).Row
  lrA = Range("A" & Rows.Count).End(xlUp).Row
  If lrA > lrTkt Then Rows(lrTkt + 1).Resize(lrA - lrTkt).Delete
End Sub

 

[tlc53]

Originally I had thought every line in column A was blank after the ticket number stopped but it was only on closer inspection, I noticed that was not the case. Sorry for not checking and being precise to begin with. Your assumption b) is also correct
Thank you very much for the new code. It works perfectly and is brilliant. Thank you!

 

[Peter_SSs]

Thank you very much for the new code. It works perfectly and is brilliant. Thank you!

You're welcome. Thanks for the confirmation.

 

[tlc53]

Hi again Peter.
I'm now applying this macro to the big scheme of things however, it's not quite behaving as I would like.
It deletes everything from the last ticket number apart from there's one row it just won't delete. It doesn't matter how many times I run it, it does not want to delete that line/those figures.
I'm attaching a copy of the file, so you can see what I mean. 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? Thank you.

https://www.dropbox.com/s/dpx44zs4ex61lt5/Delete Blank Lines - Ticket Macro.xlsm?dl=0