Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add rows to a sheet
Hi If I have this in a sheet:
1 5 8 10 15 25 27 30 What i want is a macro there can make empty rows between the number so there come 3 empty rows between 1 and 5 and 1 empty row between 8 and 10 - and so on, I don't know how many rows there are numbers in: Hope some one understand and maybe also can help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add rows to a sheet
Try this... (Put the cursor in on the first value - 1) then run the macro
until the end. Sub InsertingLines() Dim Firstvalue As Integer Dim Secondvalue As Integer Dim Result As Integer Dim NoofRows As Integer Dim Number As Integer Firstvalue = ActiveCell.Value ActiveCell.Offset(1, 0).Select Secondvalue = ActiveCell.Value Result = Secondvalue - Firstvalue NoofRows = Result - 1 For Number = 1 To NoofRows Selection.EntireRow.Insert Next Selection.End(xlDown).Select End Sub "alvin Kuiper" wrote: Hi If I have this in a sheet: 1 5 8 10 15 25 27 30 What i want is a macro there can make empty rows between the number so there come 3 empty rows between 1 and 5 and 1 empty row between 8 and 10 - and so on, I don't know how many rows there are numbers in: Hope some one understand and maybe also can help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
add rows to a sheet
I don't know if you have a header row. The code below assumes there is not header row. Let me know if there is a header row and I will make a couple of minor changes. Sub MoveRows() LastRow = Range("A" & Rows.Count).End(xlUp).Row For RowCount = LastRow To 1 Step -1 RowIndex = Range("A" & RowCount) If RowIndex < RowCount Then Rows(RowCount).Copy Destination:=Rows(RowIndex) Rows(RowCount).ClearContents End If Next RowCount End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=178433 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
add rows to a sheet
You can use this macro to do what you want...
Sub InsertRows() Dim X As Long, LastRow As Long Const FirstRow As String = 2 Const DataColumn As String = "A" LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row For X = LastRow To FirstRow + 1 Step -1 With Cells(X, DataColumn) .Resize(.Value - .Offset(-1).Value - 1).EntireRow.Insert End With Next End Sub Just set the two Const values to match your setup (FirstRow is the row number your starting number is in, DataColumn is the column letter your numbers are in), the code will adjust itself around the values you set these to. Also note that your number does not have to start at 1... the code will work around whatever your starting number is (that is, your column numbers could be 8, 10, 15, 25 and the appropriate number of rows will be inserted between them). -- Rick (MVP - Excel) "alvin Kuiper" wrote in message ... Hi If I have this in a sheet: 1 5 8 10 15 25 27 30 What i want is a macro there can make empty rows between the number so there come 3 empty rows between 1 and 5 and 1 empty row between 8 and 10 - and so on, I don't know how many rows there are numbers in: Hope some one understand and maybe also can help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can i wrap rows to form multiple rows per row to fit on 1 sheet? | Excel Discussion (Misc queries) | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Help: auto-copy entire rows from 1 sheet (based on cell criteria) to another sheet. | Excel Programming | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
VBA to count rows in a sheet and increase counter on another sheet then print | Excel Programming |