Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after last (filled row)
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... :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after last (filled row)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after last (filled row)
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after last (filled row)
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after last (filled row)
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row after last (filled row)
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 =) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Insert the present time when a cell is filled with a data, | New Users to Excel | |||
Filled cells dont appear as filled | Excel Discussion (Misc queries) | |||
zero filled changed to non-zero filled | Excel Programming | |||
Auto insert new row after current row filled in | Excel Discussion (Misc queries) | |||
insert zeros in front of cells already filled with data. | Excel Discussion (Misc queries) |