Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert letter into empty row ?
Hi there,
I have a spreadsheet with two columns Column A "level" and cloumn B "Task" and about 100 rows. I needed to insert 7 empty row between each raw and I have this macro to do this jub Option Explicit Sub InsertBlankRows() Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long Dim Rng As Range Dim lastrw As Long numRows = InputBox("How many Rows") lastrw = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A")) For r = Rng.Rows.Count To 1 Step -1 Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert Next r Application.ScreenUpdating = True End Sub now on cloumn B in the empty rows (7) I need to insert a leeters empty rows 1 B empty rows 1 B empty rows 1 B empty rows 1 B empty rows 1 B empty rows 1 B empty rows 1 B |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert letter into empty row ?
I'm sorry I didint finish my question
its sould be now on cloumn B in the empty rows (7) I need to insert a leeters empty rows 1 B empty rows 2 C empty rows 3 K empty rows 4 M empty rows 5 P empty rows 6 S empty rows 7 U so it look like this "A" "B" 1 level0 task1 2 level1 B 3 level1 C 4 level1 K 5 level1 M 6 level1 P 7level 1 S 8 level1 U 9 level0 task2 10 .................. 100 "Ed Dror" wrote in message ... Hi there, I have a spreadsheet with two columns Column A "level" and cloumn B "Task" and about 100 rows. I needed to insert 7 empty row between each raw and I have this macro to do this jub Option Explicit Sub InsertBlankRows() Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long Dim Rng As Range Dim lastrw As Long numRows = InputBox("How many Rows") lastrw = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A")) For r = Rng.Rows.Count To 1 Step -1 Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert Next r Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert letter into empty row ?
Sounds like you want to put this in you loop, just before
Next r dim x as integer ''' <<< put this at the beginning of the code. x = r +1 cells(x,2) = "B" cells(x+1)="C" cells(x+2)="K" cells(x+3)="M" cells(x+4)="P" cells(x+5)="S" cells(x+6)="U" Another way would be to put your codes in a throw-away range (lets say Range("AA1:AA7") (clear this range at the end of your code) than all you need is Range(Cells(r+1,2),Cells(r+7,2)).value=Range("AA1: AA7").value -- steveB Remove "AYN" from email to respond "Ed Dror" wrote in message ... I'm sorry I didint finish my question its sould be now on cloumn B in the empty rows (7) I need to insert a leeters empty rows 1 B empty rows 2 C empty rows 3 K empty rows 4 M empty rows 5 P empty rows 6 S empty rows 7 U so it look like this "A" "B" 1 level0 task1 2 level1 B 3 level1 C 4 level1 K 5 level1 M 6 level1 P 7level 1 S 8 level1 U 9 level0 task2 10 .................. 100 "Ed Dror" wrote in message ... Hi there, I have a spreadsheet with two columns Column A "level" and cloumn B "Task" and about 100 rows. I needed to insert 7 empty row between each raw and I have this macro to do this jub Option Explicit Sub InsertBlankRows() Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long Dim Rng As Range Dim lastrw As Long numRows = InputBox("How many Rows") lastrw = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A")) For r = Rng.Rows.Count To 1 Step -1 Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert Next r Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a letter in front of number in every cell? | Excel Worksheet Functions | |||
I WANT TO INSERT A LETTER IN FRONT OF A NUMBERS | Excel Worksheet Functions | |||
Insert letter in cell | Excel Discussion (Misc queries) | |||
Insert letter/no among word | Excel Discussion (Misc queries) | |||
Insert and sum in empty cell | Excel Worksheet Functions |