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]

You have a nice weekend also Holland. And take care:
Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

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

 

[KVT Holland]

I thought it was pretty idiot prove, but I've been asked to adjust the code so that rows that are left blank in the middle of the sheet also get erased.

I managed to do it by;

Private Sub CommandButton2_Click()

ActiveSheet.Unprotect Password:="calc1"

Do While x < 4
    Dim r As Range, c As Range

Set r = Range("Calculatie!A10:Calculatie!A80")

    Application.ScreenUpdating = False
    
For Each c In r
    If Len(c.Text) = 0 Then
        
        c.EntireRow.Select       
        Selection.EntireRow.Delete
        
    End If
Next c

x = x + 1
Loop

Application.ScreenUpdating = True

ActiveSheet.Protect Password:="calc1"

End Sub

This works and i learned a lot about VBA, but I would like to clean it up a bit to get better at VBA.

I've used a loop because at some point 'c' get set to high and it misses an empty row every now and then.

Kind regards,


Koen.