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

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
61
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
(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 ?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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).




[TABLE="width: 641"]
<tbody>[TR]
[TD][/TD]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Memo[/TD]
[TD]Event[/TD]
[TD]Department ID[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]506050, 77, 400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][/TD]
[TD]flight-swa[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][/TD]
[TD]flight-united[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]Sum for 506050, 77, 400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]Sum for 506050, 77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD]Sum for 506050[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top