How to delete entire lines if cells in pre-defined columns are up to max pre-defined quantity of characters?

[Neighbour]

Hi, I have Excel 2013 and looking to delete lines (entire lines) where cells in exact wanted column contain exact, pre-defined, quantity of characters (each cell separately of course) OR LESS. The goal is to get lines deleted where there are not enough characters in cells of analysed column. Those characters can be anything, e.g. symbols, letters, numbers, etc.? Since I'm NOT that good with Excel, I would greatly appreciate more detailed and easy to follow instructions please. Thank you in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

If your data is not too large then you could try this macro. To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

I have assumed that the column of interest does not contain formulas or, if it does, those formulas do not need to be retained.

Sub DeleteShort()
  Const ColumnOfInterest As String = "E"
  Const MinCharactersReqd As Long = 5
  
  Application.ScreenUpdating = False
  With Intersect(ActiveSheet.UsedRange, Columns(ColumnOfInterest))
    .Value = Evaluate(Replace("if(len(#)<" & MinCharactersReqd & ","""",#)", "#", .Address))
    On Error Resume Next
    .SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

If your data is very large and there are a lot of scattered rows to be deleted this may run somewhat slowly. If that is the case for you post back with an approximate number of rows in your data for an alternative solution.

 

[Neighbour]

Thank you very much for your response. I followed your instructions and saved file as macro but when I try to run that macro, I get the following error: Compile error: Invalid outside procedure

 

[Peter_SSs]

I get the following error: Compile error: Invalid outside procedure

Are you sure that you copied the whole of my code (easiest way is to use this icon at the top right of the code:

).
Does it start with Sub ... and end with End Sub
If it does then you must have some other code (before or after mine or in another module) that is incomplete.

 

[Neighbour]

I copied everything, including ''VBA Code:'' without the quotes at the beginning. However, I changed E to my wanted column and ''5'' to my wanted different minimum. So every cell with quantity of characters, in my wanted column (the one I changed E to), LOWER than defined one, could get it's entire line deleted.

 

[Peter_SSs]

I copied everything, including ''VBA Code:''

Get rid of the VBA Code:, it is a heading in the forum, not part of the code. If you use the icon I suggested to copy, you won't get that heading.

 

[Neighbour]

Worked good. Thank you once again.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.