Command button delete rows... sort of

[KVT Holland]

Hi,

I found a peace of code written bij Joe Was in:

https://www.mrexcel.com/forum/excel-questions/9165-entire-row-delete.html

I modified it to work on a command button. But it removes every other row Lets say row 11 till 18 are empty/< 1 then it removes 11, 13, 15, 17. I've tried it with a ' in front of the N = N + 1, but that didn't change anything.

Private Sub CommandButton2_Click()

ActiveSheet.Unprotect Password:="PW"


Worksheets("Calculatie").Select
Range("A11:A30").Select
n = 1
n = n + 1
For Each r In Worksheets("Calculatie").UsedRange.Rows
n = r.Row
If Worksheets("Calculatie").Cells(n, 1) < 1 Then
Worksheets("Calculatie").Cells(n, 1).Select
Selection.EntireRow.Delete
End If
Next r
Application.ScreenUpdating = True
Range("A11").Select


ActiveSheet.Protect Password:="PW"


End Sub

Kind regards,

Koen.

 

[My Aswer Is This]

Are you wanting to delete any row if there is no value in column "A" ?

And you want to delete the entire row?

 

[KVT Holland]

I tried deleting the entire row if column A was empty, but then i found this and knew it should work. All useful number are larger than 1.

The entire row needs to be deleted, or at least the part of the row from A till I.

 

[My Aswer Is This]

Try this:

Will delete entire row if column "A" is empty

Sub Delete_Me()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Lastrow To 1 Step -1
        If Cells(i, 1).Value = "" Then Rows(i).Delete
    Next
Application.ScreenUpdating = True
End Sub

 

[KVT Holland]

I found a simular peace of code, but it couldn't get get it to start working on row 10.

A few lines in the first row are empty and because the entire sheet gets locked any way i just used places a 2 and used a white font....

I know its bad, but i'm still learning.

Lets see if it works better now i have the white font number in the empty cells. Hadden't thought about it before.

 

[KVT Holland]

Hey that worked.

Thanks!

 

[My Aswer Is This]

It does not matter what color the font is. Did you try my script. It works on all rows you never said start on row 10.

If you want to start on row 10 change this:

For i = Lastrow To 10 Step -1

I found a simular peace of code, but it couldn't get get it to start working on row 10.

A few lines in the first row are empty and because the entire sheet gets locked any way i just used places a 2 and used a white font....

I know its bad, but i'm still learning.

Lets see if it works better now i have the white font number in the empty cells. Hadden't thought about it before.

 

[KVT Holland]

Thank a lot, i never realized i could use the Lastrow like that. That's gonna help clean up some other 'code' 'I' made.

How can i mark this post as solved?

 

[My Aswer Is This]

You just did by your comment "solved"

Thank a lot, i never realized i could use the Lastrow like that. That's gonna help clean up some other 'code' 'I' made.

How can i mark this post as solved?

 

[KVT Holland]

Good to know. Thanks for the help and enjoy the weekend