Delete rows depending on number of columns

[gildartzz]

Hi, I would like to know how one can delete multiple rows depending on the number of columns that they have.
For eg. say I have 100 rows and the number of columns with data are different for each row. Now I only want rows that have exactly 10 columns with data and delete the ones that dont meet this requirement.

 

[kweaver]

What about this? I only looked at columns A to Z...change as needed.

Sub Keep10()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = lr To 2 Step -1
If WorksheetFunction.CountA(Range("A" & i & ":Z" & i)) <> 10 Then
Rows(i).EntireRow.Delete
Else
End If
Next i
End Sub

 

[kweaver]

I think the line calculating lr should be changed to this:

lr = ActiveSheet.UsedRange.Rows.Count

I made a bad assumption.

 

[gildartzz]

Hey kweaver. thanks for your response before. I tried running the code you posted but the result was that all data except the first row was removed.

 

[gildartzz]

Hey kweaver. thanks for your response before. I tried running the code you posted but the result was that all data except the first row was removed.

this image is an example of the data I have. there over 500000 rows in the actual dataset. I want to keep only those rows that have exactly 10 entries, and delete the rest.

 

[kweaver]

Did you change the routine in any way? It has worked for me with dozens of rows and hundreds of rows.

 

[gildartzz]

Did you change the routine in any way? It has worked for me with dozens of rows and hundreds of rows.

not much. just changed A:Z to B:S

 

[gildartzz]

not much. just changed A:Z to B:S

Also when I ran it the first time it kept running for close to 3 hrs. Then I tried it running it on a smaller sample and the result was what I mentioned above.

 

[kweaver]

I think all of the rows would be deleted in your example because from B to S there are always more than 10 data values.

Also, please install XL2BB so that your data can be easily copied rather than recreating.

I just ran it with 1000 rows (plus the header) and it took seconds and seems to work just fine keeping only the rows with 10 entries from B to S.

 

[kweaver]

Another way to do this would be using filter but I haven't tried that. Might be faster.

On my PC it took about 15 seconds to run with 2000 rows.