Insert a blank row

[VinodKrishnappa]

Hi I am looking for macro help where i can insert a blank row in range of cells based on non blank cells of the above cells.

For example after filling data in Range (A2:O2) completely, with a macro help i want insert a blank row down (i.e., A3:O3).

Could any one help me on this.

 

[Joe4]

To insert a blank line just below your active cell, just use this one line of VBA code:

    Selection.Offset(1, 0).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

 

[VinodKrishnappa]

Hi I am looking for macro help where i can insert a blank row in range of cells based on non blank cells of the above cells.

For example after filling data in Range (A2:O2) completely, with a macro help i want insert a blank row down (i.e., A3:O3).

Could any one help me on this.

Hi thank you.

My requirement was not clear in the initial post.

I have a invoice file in which a range of product details to be filled.

Range starts from cell "A19" to "O19", it consists Product description, tax columns, amount columns ect... Immediately after this row i'll be having a total row of the product billing value in cell "O20"

I need to make available an option to insert a blank line between row 19 & 20 if there is more than one item & so on. This should enable only after updating complete details of 1st product in 1st line.

Now the 2nd line should also follow the same format of the 1st line & total billing value in cell "O20" which will shift to "O21" should consider the total value of both products value in total.

Hope this clarifies my requirement. I'm unable to paste the work sheet details on this post.

Please do the needful.

 

[Joe4]

Try this code. When you run it, it will prompt the user to enter the row number in which they want to insert a new row under.
If the row number the enter is not populated in all columns A-O, it will not insert a new row.

Sub InsertBlankRow()

    Dim insRow As Long
    Dim myRange As Range
    
    On Error GoTo err_exit
'   Prompt to insert row
    insRow = InputBox("Which row would you like to insert a new row after?", "INSERT ROW")
    
'   Make sure all cells in columns A:O are populated
    Set myRange = Range(Cells(insRow, "A"), Cells(insRow, "O"))
    If Application.WorksheetFunction.CountBlank(myRange) = 0 Then
'       Insert new row
        Rows(insRow + 1).Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Else
        MsgBox "Cannot insert row until all columns A-O in " & insRow & _
                " are populated!", vbOKOnly, "PROCESS ABORTED!"
    End If
    
    On Error GoTo 0
    
    
    Exit Sub
    
err_exit:
    MsgBox "You have not entered a valid row number", vbOKOnly, "ENTRY ERROR!"
    
End Sub

 

[VinodKrishnappa]

Hi Joe4,

Thank it works good. How to freeze the 1st row of data entry in the code?

My data entry starts in the sheet from 19th row & total will be in 20th row (if there is no additional line item or if there is only 1 product to bill)

In this code can you please give me some changes so that a row can inserted between 19th row & 20th row if there is a 2nd product to bill, so on for 3rd product after 2nd.

Only 1 row shall be inserted at once after updating the above row completely.

 

[Joe4]

How to freeze the 1st row of data entry in the code?

When you say "freeze" are you referring to this: https://support.office.com/en-us/ar...-columns-dab2ffc9-020d-4026-8121-67dd25f2508f
or this: https://stackoverflow.com/questions/3037400/how-to-lock-the-data-in-a-cell-in-excel-using-vba

My data entry starts in the sheet from 19th row & total will be in 20th row (if there is no additional line item or if there is only 1 product to bill)

In this code can you please give me some changes so that a row can inserted between 19th row & 20th row if there is a 2nd product to bill, so on for 3rd product after 2nd.

Only 1 row shall be inserted at once after updating the above row completely.

I don't understand. That is what the code does, doesn't it? What part of it isn't working correctly for you.
The only difference I can see is that my code prompts the user to enter the row number where the new row should be entered. We cannot hard-code it at 19-20, because that can change.
If you want to have the code figure out where to insert the rows automatically without the prompt, please explain the logic behind determining where to insert the new row.

 

[VinodKrishnappa]

Hi

I've enclosed the file in my drive (link is below) for your reference. Your code is very much working, I’m very much thank full for that. I just wanted to make it as simple as possible for the users of this file, as users of this don's understand row numbers and all. Anyhow that is my part to make them understand.

https://drive.google.com/file/d/0Bx1N6PJF4FliV3NlM2RMajJsMm8/view?usp=sharing

Now actually I’ve something else to ask in addition. In the enclosed file if the number of products to be billed is more than 8, then print area adjustment will be very difficult. I'm thinking to print it in two or more pages.

Here in printing of first page header should from cell A1 to O15, then the list of first 8 (from Sl. No. 1 to 8) (products to be billed in body with subtotal, then footer from cell A35 to cell O50.

In 2nd page again header should be from cell A1 to O15, then the list of next 8 products (from Sl. No. 9 onwards), then grand total of all the products (including from 1^st page) with amount in words, then again footer from cell A35 to O50.

If the items are more than 8, header & footer shall be constant in all the print pages. Subtotal of all the in each sheet & then grand total in last sheet with amount in words of grand total.

Can you suggest me to make it happen with macro or can you share some links to find some stuff how to achieve this task.

 

[Joe4]

If you have a new question, it is best to ask it in a new thread (even if it is on the same project).

The general rule of thumb is this, if it is a follow-up question where it is essential that is dependent upon the previous answers, then post it to the same thread.
However, if it is a new question, post it to a new thread so others can see that you now have a new unanswered question (especially since there is no guarantee that the person who helped you with the previous question can/will help you with the next). Especially since I do not have the ability to download files from my current location!