Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Rij met tekst vastzetten


Excel 2007 Vba-code voor


In rij 10 staat tekst vermeld, deze moet in rij 10 blijven staan ​​ook als er rijen worden toegevoegd of rijen worden verwijderd.

Dank bij voorbaat 




Sorry but I don't speak Dutch (and you'll get more help if you post your question in English).

Google Translate thinks your question is:

Title: Excel Freeze row with text  

Excel 2007 Vba code for  

Example   Row 10 contains text, it should remain in row 10 even if rows are added or rows are deleted.  

Thanks in advance

I'm not sure what you mean- do you mean row 10 should not be deleted? Or that row 10 remains as  row 10 even if rows above are added or deleted? (If so, if a row is added, which row should be removed to allow row 10 to stay?) What happens if cells above row 10 are deleted? (Is the row 10 text in a single cell so the deletion only affects if if in the same column?)

It always help if you can post an Excel file. Please edit your original question to clarify your question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data (and any macros). Then we should be able to give specific help.
John_Ru (rep: 6297) Jun 29, '24 at 6:40 am
Add to Discussion


Selected Answer


Your Question is in Dutch but (with the help of Google Translate) I guess you want VBA code so that set text in row 10 stays in row 10 if entire rows are deleted. I'm not sure what you want to do when rows are added or single cells are added/deleted (see Discussion under the Question above).

I'm not aware of a direct way to do that but in the attached demo file, there is sample text in rows 1 to 9 and "Protected Text" in row 10. In hidden column O (15), numbers 1 to 10 are in sequence in rows 1 to 10- these are used to detect which rows are deleted (and could be used for those added).

The Worksheet_Change event code below acts when entire rows are deleted (and gives a message if some are added). The comments tell you what happens...

Private Sub Worksheet_Change(ByVal Target As Range)

    ' check last used row in hidden column 15 (=O)
    LstRw = Cells(Rows.Count, 15).End(xlUp).Row

    ' do nothing if rows above 10 weren't changed
    If LstRw = 10 Then Exit Sub
    ' prevent retriggering while this code works

    Application.EnableEvents = False

    ' if row 10 was deleted, undo the change
    If Cells(LstRw, 15).Value <> 10 Then
        MsgBox "Deletion blocked! You must not delete row 10"
        Application.EnableEvents = True
        Exit Sub
    End If

    ' if rows were deleted...
    If LstRw < 10 Then
        ' ... jump to procedure for deleted rows
        Call RowsDeleted
        '... otherwise jump to procedure for added rows
        Call RowsAdded
    End If

    Application.EnableEvents = True
End Sub

The bold lines are because the declaration  Dim LstRw As Long is at the top so that variable is "known" to the other procedures called by the other bold lines.

Correction #1: 29 June 2024

The above code (and revised file) has an extra line (in bold below) so that events are enabled if a deletion is blocked:

    ' if row 10 was deleted, undo the change
    If Cells(LstRw, 15).Value <> 10 Then
        MsgBox "Deletion blocked! You must not delete row 10"
        Application.EnableEvents = True
        Exit Sub
    End If

The RowsDeleted procedure acts when rows (up to and including row 9) are deleted:

Private Sub RowsDeleted()

    ' loop down rows
    For n = 1 To LstRw - 1
        With Cells(n, 15)
            ' see which rows were deleted...
            If .Value <> n Then
                '... and restore row / index...
                .Offset(-1, 0).Value = n
                ' ... and reduce row count
                n = n - 1
            End If
        End With
    Next n

End Sub

If a row is deleted, it just becomes blank (apart from in hidden column O) and row 10 stays where it was.

The code for insertions just gives a message for now:

Private Sub RowsAdded()
    'to be defined
    MsgBox "Rows added- what to do now?"
End Sub

Hope this makes sense and helps- if so, please remember to mark this Answer as Selected.



Seems that's what you wanted. Thanks for selecting my Answer, Seppe. 
John_Ru (rep: 6297) Jun 29, '24 at 11:30 am
Seppe- please see Correction #1: 29 June 2024 to my Answer and the revised file- this corrects an oversight on my part, sorry.
John_Ru (rep: 6297) Jun 29, '24 at 4:17 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login