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

[bbalch]

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?

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.

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

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

Thanks,
Ben

 

[Rick Rothstein]

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]

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]

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

 

[Rick Rothstein]

(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]

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:

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]

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:

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

 

[bbalch]

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 A	Col B	Col C	Col D	Col E
		Memo	Event	Department ID	Amount
Row 1	506050, 77, 400
Row 2		flight-swa	77	400	100
Row 3		flight-united	77	400	200
Row 4	Sum for 506050, 77, 400				300
Row 5	Sum for 506050, 77				300
Row 6	Sum for 506050				300

<tbody>
</tbody>