VBA - delete rows only in specific range

[Worf99]

Hi to everyone and Happy New Year.

I wrote a very simple VBA code (associated to a hot key CTRL+D) to delete the current row in a sheet:

Sub del_row()


Set foglioattivo = ActiveWorkbook.ActiveSheet


Rows(ActiveCell.Row).Delete


End Sub


I would like this to work only from row 9 to row 40, so that if the users mistakenly hit CTRL+D on the first rows it doesn't delete anything.

How can I do that?


Thank you very much.
Matt

 

[KKaren]

this should work

Sub del_row()
    
    If ActiveCell.Row >= 9 And ActiveCell.Row <= 40 Then
        Rows(ActiveCell.Row).Delete
    End If

End Sub

 

[Worf99]

Hi KKaren,
that works perfectly!!!! Thank you so much!!!!

In the rows the users can delete with the hot key there are input data and formulas, so to prevent accidental deletes by the users, I pass-protected the sheet.

My code is actually this below, the sub has to remove the password and activate it again after the delete invoked by the hot key.
It takes about half a second or even slighy longer.
I'm wondering if there is a faster way to achieve this...

Sub cancella_riga()




Set foglioattivo = ActiveWorkbook.ActiveSheet


'sproteggo il foglio
ActiveSheet.Unprotect Password:=" "
Call AutoFiltroOff


'cancella la riga
If ActiveCell.Row >= 9 And ActiveCell.Row <= 100 Then
Rows(ActiveCell.Row).Delete
End If


'riproteggo il foglio
ActiveSheet.Protect Password:=" ", AllowFiltering:=True
End Sub

---------------------------------------------------------------------

Public Sub AutoFiltroOff()


Dim wb As Workbook
Dim ws As Worksheet


Set wb = ThisWorkbook
Set ws = wb.ActiveSheet


'azzera le condizioni del filtro automatico
'e mostra tutti i dati
If ws.AutoFilterMode Then
If ws.FilterMode Then
ws.ShowAllData
End If
End If


Set ws = Nothing
Set wb = Nothing


End Sub

 

[MARK858]

How much difference does just turning screenupdating off make?

Sub cancella_riga()


    Set foglioattivo = ActiveWorkbook.ActiveSheet

    Application.ScreenUpdating = False
    'sproteggo il foglio
    ActiveSheet.Unprotect Password:=" "
    Call AutoFiltroOff


    'cancella la riga
    If ActiveCell.Row >= 9 And ActiveCell.Row <= 100 Then
        Rows(ActiveCell.Row).Delete
    End If


    'riproteggo il foglio
    ActiveSheet.Protect Password:=" ", AllowFiltering:=True
    Application.ScreenUpdating = True
End Sub

Public Sub AutoFiltroOff()


    Dim wb As Workbook
    Dim ws As Worksheet


    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet


    'azzera le condizioni del filtro automatico
    'e mostra tutti i dati
    If ws.AutoFilterMode Then
        If ws.FilterMode Then
            ws.ShowAllData
        End If
    End If


    Set ws = Nothing
    Set wb = Nothing


End Sub

 

[Worf99]

Hi and thank you.
I didn't know that command and it helps in other part of my file.
The part I'm working on is slow because it depends on a web query.
I think I need to halt the calculation and resume it but I have to be careful.
Thanks again!