Solved Add row in excel file

November 17, 2015 at 14:45:46
Specs: Windows 7
Hi, so I have this sheet where i insert data in rows daily and i need excel to automatically add new rows below. For instance i want to delete all rows and start the document with 2 empty lines, and as i use those lines, excel will add new ones below.
This is just for the document to be "prettier" when working with and have only the needed lines instead of a all bunch of empty white rows.

message edited by salvador89


See More: Add row in excel file

Report •

✔ Best Answer
November 18, 2015 at 07:03:07
Here is an example of how it can be done, but you will need to test and tell us what you need the code to do to meet your requirements.

Assuming you are using 'Sheet1'

Open Excel
Press and hold Alt on your keyboard and press F11
When the window opens
double click on sheet1
paste the code

Now go to sheet1 of Excel
highlight row 2
Hold Ctrl and Shift together then press the down key
This will highlight ALL rows apart from 1
now right click on the number 2 of row 2 and press hide

Now whenever you enter something in column 1 and then enter to select out of that cell, two new rows will be unhidden below.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 1 Then

        For i = 1 To 2
            Sheet1.Rows(ActiveCell.Row + i).EntireRow.Hidden = False
        Next i
    
    End If
    
End Sub

Give it a go and see if it does what you need.



#1
November 17, 2015 at 15:07:49
If the Excel sheet is at the end of its document, it should have many free rows left. Would it be possible for you to include some screenshots on exactly what it is you're wanting to do? Not quite sure I understand.

20 years in IT and counting, baby
\m/ > < \m/


Report •

#2
November 17, 2015 at 15:11:18
I just updated the question...I believe is more understandable now...:)

Report •

#3
November 17, 2015 at 15:17:40
heres a screen shot...i want excel to add automatically so i always have 2 empty rows at the end...
<img src='http://s30.postimg.org/ccju58nod/excel.jpg' border='0' alt="excel" />

message edited by salvador89


Report •

Related Solutions

#4
November 18, 2015 at 07:03:07
✔ Best Answer
Here is an example of how it can be done, but you will need to test and tell us what you need the code to do to meet your requirements.

Assuming you are using 'Sheet1'

Open Excel
Press and hold Alt on your keyboard and press F11
When the window opens
double click on sheet1
paste the code

Now go to sheet1 of Excel
highlight row 2
Hold Ctrl and Shift together then press the down key
This will highlight ALL rows apart from 1
now right click on the number 2 of row 2 and press hide

Now whenever you enter something in column 1 and then enter to select out of that cell, two new rows will be unhidden below.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 1 Then

        For i = 1 To 2
            Sheet1.Rows(ActiveCell.Row + i).EntireRow.Hidden = False
        Next i
    
    End If
    
End Sub

Give it a go and see if it does what you need.


Report •

Ask Question