Insert row after last (filled row)

[stannynuytkens]

Hello guys,

I'm having problems at programming a piece to add a printer to a
list...
The first printers (31 of them) are allready filled. They are given an
ID represented by
a simple number (eg. 1st printer row has number 1, 2nd printer row has
number 2, etc...).
The indexing begins at A5 with the number 1, each printer has now 6
topics (columns): "ID, number, IP, Description, Location, Date of first
use". Now ive added a button "Add new printer" and i want to have a sub
in a module that finds the last number (in this case 31, because the
cell below it, is empty) and insert a new row in it.
Now the sub will have to loop until he finds an empty cel in the A
column starting from A5. The row must be inserted below the last filled
row and must copy the cell formatting of the row above.

Any help very much appreciated...any questions gladly answered.


greetings from Belgium

Stanny

PS: After this works i'll come with another question...

 

[merjet]

Dim iRow As Long
iRow = Sheets("Sheet1").Range("A5").End(xlDown).Row
Sheets("Sheet1").Range(Cells(iRow, 1), Cells(iRow, 6)).Copy
Sheets("Sheet1").Range(Cells(iRow + 1, 1), Cells(iRow + 1,
6)).PasteSpecial _
Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Modify "Sheet1" and "6" to suit.

Hth,
Merjet

 

[Don Guillett]

try this
Sub copyline()
With Range("a22").End(xlDown)
.Copy .Offset(1)
End With
End Sub

for the whole row
Sub copyline()
With Range("a22").End(xlDown)
.entirerow.Copy .Offset(1)
End With
End Sub

 

[grmbl]

merjet schreef:

Dim iRow As Long
iRow = Sheets("Sheet1").Range("A5").End(xlDown).Row
Sheets("Sheet1").Range(Cells(iRow, 1), Cells(iRow, 6)).Copy
Sheets("Sheet1").Range(Cells(iRow + 1, 1), Cells(iRow + 1,
6)).PasteSpecial _
Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Modify "Sheet1" and "6" to suit.

Hth,
Merjet

Hey, thx.... i managed to make it work by adjusting the
Range(Cells(iRow + 1,1) to
Range(Cells(iRow + 2,1) but the dotted line (copying format) stays on
the screen...
Now, i have to increment the number (ID) by 1 and fill the other five
cells with variable values...
Yes, im demanding but i learn from editing existing code. (NO, im NOT a
hacker, just a hard student)

greetings, and thx again Merjet

 

[grmbl]

here a little screenie:

http://img444.imageshack.us/my.php?image=excel0dh.jpg

cheers

 

[merjet]

Undo the copying mode with:
Application.CutCopyMode = False

I don't understand why you needed iRow + 2.
In any case, the incrementing and filling would go something like this:
Sheets("Sheet1").Cells(iRow+1,1) = Sheets("Sheet1").Cells(iRow,1) + 1
Sheets("Sheet1").Cells(iRow+1,2) = 'add code
.. . .
Sheets("Sheet1").Cells(iRow+1,5) = 'add code

Hth,
Merjet

 

[grmbl]

merjet schreef:

Undo the copying mode with:
Application.CutCopyMode = False

I don't understand why you needed iRow + 2.
In any case, the incrementing and filling would go something like this:
Sheets("Sheet1").Cells(iRow+1,1) = Sheets("Sheet1").Cells(iRow,1) + 1
Sheets("Sheet1").Cells(iRow+1,2) = 'add code
. . .
Sheets("Sheet1").Cells(iRow+1,5) = 'add code

Hth,
Merjet

The original code didn't add a row but when i changed it to 2 it
dit.... =/
As far as im concerned the problem is solved!

Ive allready completed the code now but hey nice of you to help me
Merjet.
You've just learned me a bit more about VBA, thx!

greetlings =)