How do I insert a line but when trying to insert comes up with error

[ghrek]

Hi

Im trying to insert a blank row below a row that has the word "Ticket Control No." in column S.

When I tried doing this manually I get the error message

"microsoft excel cant insert new cells because it would push non empty cells off the end of the worksheet"

On my worksheeet there is data in columns A-AX and rows 1 -90.

What I mean for instance is where S5 says "Ticket Control No." I need a blank row inserted below in row 6. I need it to look all the way down the workbook and insert as necessary.

1) Why is is telling me I cannot do as the workbook has loads of blank rows/columns

2) Can this be automated by a macro?

Thanks

 

[Joe4]

It you sounds like you have applied formulas or something to the ENTIRE worksheet, so Excel thinks that every row is already being used. So inserting any other rows would push stuff of the page (or so Excel thinks), and it won't allow that to happen.

So, in order to change that, you first need to delete all the rows below your REAL end of data, down to the end of the worksheet.

 

[ghrek]

AAHHHHH right Ive given that a try and that seems to be issue.

What I need to know then if poss is the following.

Am I able to highlight and clear contents from blank cells in the rest of the workbook below the last line of data as it will be different amounts of data in the workbook as they are downloads.

Can I automatically insert line via macro as not got a clue where to start.

 

[Joe4]

Am I able to highlight and clear contents from blank cells in the rest of the workbook below the last line of data as it will be different amounts of data in the workbook as they are downloads.

If the cells are already blank, what exactly would we be clearing from them?

 

[ghrek]

If the cells are already blank, what exactly would we be clearing from them?

It must be some sort of formula because when I highlighted cells and clicked on Clear Contents it worked OK

 

[Joe4]

It must be some sort of formula because when I highlighted cells and clicked on Clear Contents it worked OK

If you look at such cells before clearing them, do you see anything (like a formula) in them?

For rows that actually have data in them, is there some column (like maybe column A) that will ALWAYS have data, so we can use that one to determine where the actual last row of data exists?
Let's say it is column A. If you run this simple code, does it seem to return the right value?

Sub Test()
    MsgBox "The last row with data is " & Cells(Rows.Count, "A").End(xlUp).Row
End Sub

 

[ghrek]

Give that a try and yes returns right result. The column that will always have data in every row will be column "W"

Looking at whats in the columns ive noticed that there is " - " all the way down column H. This is for value and thus would be 10-00 but just - if no value in the cell

 

[Joe4]

This is for value and thus would be 10-00 but just - if no value in the cell

Are you sure that just isn't the result of Custom Formatting?
Or do you see a formula in that cell?

This should delete all rows underneath the last row of data:

Sub DeleteRows()

    Dim row1 As Long, row2 As Long
    
    row1 = Cells(Rows.Count, "W").End(xlUp).Row + 1
    row2 = Rows.Count
    
    Rows(row1 & ":" & row2).Delete
    
End Sub

Then you should be able to insert rows without issue.