code not working

steve hill

Board Regular
Joined
Jul 11, 2006
Messages
156
Office Version
  1. 365
Platform
  1. Windows
Hi I got the code below someone on this site about a year ago but seems to have stopped working, when I run the macro I get the selection box, I then select the require range in column A this is entered into box, when I click ok nothing else happens. I can only assume its not excepting my selection. the only thing diffrent from last year is I have open last years file and saved as changing 2007 to 2008 in file name, any help would be appreciated

Code:
Sub finished_make_selection_to_add_blank_rows()
'Erik Van Geit
'060630

Dim tmp As Range
Dim rng As Range
Dim FR As Long          'First Row
Dim LR As Long          'Last Row
Dim CR As Long          'Count Rows
Dim NR As Integer       '# rows to insert

    On Error Resume Next
    Set tmp = Application.InputBox(prompt:="Select the range where you want to insert rows", _
    Title:="SELECTION", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    If tmp Is Nothing Then Exit Sub

FR = tmp(1).Row
CR = tmp.Rows.Count
LR = FR + CR - 1

    NR = Application.InputBox("Please enter the number of rows to insert", "# ROWS", Type:=1)
    If NR = False Then
    MsgBox "No rows will be inserted", 48, "Operation aborted"
    Exit Sub
    End If

Application.ScreenUpdating = False

Columns(1).Insert
Set rng = Range(Cells(FR, 1), Cells(LR, 1))

    With rng
    Cells(FR, 1) = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
    Rows(LR + 1 & ":" & LR + NR * CR).Insert Shift:=xlDown
    .Copy .Offset(CR, 0).Resize(CR * NR, 1)
    .Resize(CR * (NR + 1)).EntireRow.Sort Key1:=Cells(FR, 1), Order1:=xlAscending, Header:=xlNo
    .EntireColumn.Delete
    End With

Application.ScreenUpdating = True

End Sub

thanks
steve
 
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Can you put an apostrophe in front of the On Error Resume Next line, and then run the code and tell us what happens?
 
Upvote 0
Can you put an apostrophe in front of the On Error Resume Next line, and then run the code and tell us what happens?

hi Rory
tried this and got a object not required error box

steve
 
Upvote 0
How are you selecting the range in question? It would seem to be a problem with the range as you suspected.
 
Upvote 0
How are you selecting the range in question? It would seem to be a problem with the range as you suspected.

Hi
I am clicking on the first line and dragging down to the last line

steve
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top