VBA to Delete Rows if Cell Length in Column A = 14

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
53
Hi everyone,

I'm trying to modify the VBA found: https://www.rondebruin.nl/win/s4/win001.htm to delete rows based on the cells length instead of the cells content. The website's example deletes rows that contain "ron" and the VBA works as expected. I would to like to modify so that it deletes the entire row if the cell's length in column A = 14.

Any suggestions on how to modify this code to Delete the Entire Row where the Cell Length in Column A = 14?

Code:
Dim Firstrow As LongDim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long




With ActiveSheet
    .Select
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    .DisplayPageBreaks = False


    Firstrow = .UsedRange.Cells(7).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


    For Lrow = Lastrow To Firstrow Step -1


        With .Cells(Lrow, "A")
            If Not IsError(.Value) Then
                If .Value = "ron" Then .EntireRow.Delete
            End If
        End With


    Next Lrow


End With
I've tried these adjustments and receive a "Run-time error '438': Object doesn't support this property or method error message.

Code:
 With .Cells(Lrow, "A")            
            If Not IsError(.Value) Then
                If .Len = "14" Then .EntireRow.Delete
            End If
        End With
Code:
 With .Cells(Lrow, "A")           
           If Not IsError(.Len) Then
               If .Len = 14 Then .EntireRow.Delete
           End If
End With

Thanks,
Ben
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
Your question is not clear. Cells do not have "length"... they have height and width. But these values are not individually unique to each individual cell. If a cell has a certain height, then every cell in that row has the same height. Similarly, if a cell has a certain width, then every cell in that column has the same width. With this all in mind, can you describe what you are looking to do in more detail please?
 

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
53
Thank you for your help.

I'm trying to modify the VBA to delete the entire row if the formula "=LEN(A8)" (A* through the last row) returns the value of "14". So i think I'm trying to use Cells incorrectly. The first set of VBA above works it's way up from the bottom deleting the rows if they contain the .Value = "ron" in Column A. I would like to do the same thing but replace the ".Value = "ron" " with the appropriate "LEN" substitute (whatever that may be).

Actual Application of the Code:

I have system generated report that has "descriptions" in column A and the "values" in columns B-F. The system generates 3 total rows for each department when only 1 is needed. For example, the report generates the total rows as:

(The descriptions in Column A)
1) "Sum for 506050, 55 ,400" this is the row to keep,
2) "Sum for 506050" this is where the LEN = 14 and should be deleted, and
3) "Sum for 506050,55" LEN = 18 and this should also be deleted.

* There are blank rows in column A that should not be deleted.
* The rows that contain "Sum for xxxxxx,xx,xxx" in Column A are the only ones that need to be deleted.

The number combinations will change and that's why I was trying to use LEN (length) to delete rows based on the value returned. i.e. 501150,77,100 506050,99,100 etc.
 

Excel Planet

New Member
Joined
Nov 16, 2017
Messages
26
Try below

Dim Last_Row As Long

Last_Row = Cells(Rows.Count, 1).End(xlUp).Row 'Last row is determined through column A

Application.ScreenUpdating = False

For i = Last_Row To i = 2 Step -1 'excluding headers

If Len(Cells(i, 1)) = 14 Then Rows(i).Delete

Next

Application.ScreenUpdating = True
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
(The descriptions in Column A)
1) "Sum for 506050, 55 ,400" this is the row to keep,
2) "Sum for 506050" this is where the LEN = 14 and should be deleted, and
3) "Sum for 506050,55" LEN = 18 and this should also be deleted.
My first answer does not really apply to your question. I was thrown off when you said in your original message that you were trying to delete rows "based on the cells length instead of the cells content". You are actually trying to delete rows based on the cells content.. the length of the text in the cell. Anyway, can you explain what #3 above should be deleted? What is the "rule"... not the length one you are assuming, but what is it about that line that says to you "delete me"? And how is that to be distinguished from the text in example #1 ?
 

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
53
When I run this I receive the following Run Time Error "Run-time error '1004': Application-defined or object-defined error"

When debugging this code is highlighted:

Code:
If Len(Cells(i, 1)) = 14 Then
If I choose "End" on the error message instead of "Debug" it will delete the correct rows.
 

Excel Planet

New Member
Joined
Nov 16, 2017
Messages
26
When I run this I receive the following Run Time Error "Run-time error '1004': Application-defined or object-defined error"
When debugging this code is highlighted:
Code:
If Len(Cells(i, 1)) = 14 Then
If I choose "End" on the error message instead of "Debug" it will delete the correct rows.
Apology, kindly try adjusted code below:

Dim Last_Row As Long
Last_Row = Cells(Rows.Count, 1).End(xlUp).Row 'Last row is determined through column A
Application.ScreenUpdating = False

For i = Last_Row To 2 Step -1 'excluding headers (no need for 2nd i)

If Len(Cells(i, 1)) = 14 Then Rows(i).Delete

Next i

Application.ScreenUpdating = True
 
Last edited:

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
53
That did the trick. Thank you both!

Rick, below is a sample from the report I'm trying to modify. Column A contains 1 of 5 things: an account number, event, and dept # (Row 1) LEN = 15, blanks (Rows 2 and 3) LEN = 0, SUM for account number, event, and dept (Row 4) LEN = 24 this is the row to keep, SUM for account number, event (Row 5) LEN = 18 (DELETE ROW), and SUM for account number, event, and dept (Row 6) LEN = 14 (DELETE ROW).

As you can see below the Amount is the same for Rows 4, 5, and 6 and make the report too long when there are multiple accounts shown. The LEN for the rows I want to delete will always be equal to 14 or 18. That's why I was looking for something to delete based on the cells Length (LEN).




Col ACol BCol CCol DCol E
MemoEventDepartment IDAmount
Row 1506050, 77, 400
Row 2flight-swa77400100
Row 3flight-united77400200
Row 4Sum for 506050, 77, 400300
Row 5Sum for 506050, 77300
Row 6Sum for 506050300

<tbody>
</tbody>
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top