InsertRowsAndFillFormulas

[Leslie Barberie]

I am using the macro mentioned in the subject. The problem is that it will only insert a row if the active cell is in the last filled row. I want the new row to be inserted after the last filled row, but there are multiple users of this worksheet and there is no way of knowing where they will leave the active cell. Is there a way for the active cell to be moved to the last filled row when the worksheet is closed? That way the active cell would be in the right place when the worksheet is opened and the new row would be inserted in the right place. Or something like that

Thanks

Leslie Barberi

 

[Bernie Deitrick]

Leslie,

There is no standard InsertRowsAndFillFormulas macro, so posting the code
would help.

If you are game to try modifying it sight-unseen, then you could start the
macro with the line

Cells(65536, ActiveCell.Column).End(xlUp).Select

and that may work, since it selects the last filled cell in the current
column.

HTH,
Bernie
MS Excel MVP

I am using the macro mentioned in the subject. The problem is that it

will only insert a row if the active cell is in the last filled row. I want
the new row to be inserted after the last filled row, but there are multiple
users of this worksheet and there is no way of knowing where they will leave
the active cell. Is there a way for the active cell to be moved to the last
filled row when the worksheet is closed? That way the active cell would be
in the right place when the worksheet is opened and the new row would be
inserted in the right place. Or something like that?

 

[Leslie Barberie]

Bernie

I apologize for not including the code....senior moment. It is code that I copied from David McRitchie's website

Private Sub Workbook_Open(
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.ht
' Re: Insert Rows -- 1997/09/24 Mark Hill <[email protected]
'Dim vRows As Intege
' row selection based on active cell -- rev. 2000-09-02 David McRitchi
' ActiveCell.Offset(1, 0).Selec

ActiveCell.EntireRow.Select 'So you do not have to preselect entire r
vRows =

' If vRows <> 1 The
' vRows = Application.InputBox(prompt:=
' "How many rows do you want to add?", Title:="Add Rows",
' Default:=1, Type:=1) 'type 1 is numbe
' If vRows = False Then Exit Su
' End I

'if you just want to add cells and not entire row
'then delete ".EntireRow" in the following lin

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheet
Dim sht As Worksheet, shts() As String, i As Intege
ReDim shts(1 To Worksheets.Application.ActiveWorkbook.
Windows(1).SelectedSheets.Count
i =
For Each sht In
Application.ActiveWorkbook.Windows(1).SelectedSheet
Sheets(sht.Name).Selec
i = i +
shts(i) = sht.Nam

Selection.Resize(rowsize:=2).Rows(2).EntireRow.
Resize(rowsize:=vRows).Insert Shift:=xlDow

Selection.AutoFill Selection.Resize(
rowsize:=vRows + 1), xlFillDefaul

On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/0
' to remove the non-formulas -- 1998/03/11 Bill Manvill
Selection.Offset(1).Resize(vRows).EntireRow.
SpecialCells(xlConstants).ClearContent
Next sh
Worksheets(shts).Selec

End Su

Leslie Barberie

 

[Bernie Deitrick]

Try this: Change

ActiveCell.EntireRow.Select 'So you do not have to preselect entire row

To

Range(65536, ActiveCell.Column).End(xlUp).EntireRow.Select 'So....

HTH,
Bernie
MS Excel MVP

Bernie,

I apologize for not including the code....senior moment. It is code that

I copied from David McRitchie's website.

 

[Leslie Barberie]

I made the code change and I'm getting Run Time Error 1004 - Method Range of Object_Global failed.

 

[Bernie Deitrick]

Leslie,

As you should have, since I was brain-dead <g>.

Simply change the word Range to Cells: i.e., change

Range(65536, .....

to

Cells(65536, .....

Sorry about that,
Bernie

I made the code change and I'm getting Run Time Error 1004 - Method Range

of Object_Global failed.

 

[Leslie Barberie]

Thanks, Bernie. That worked. And, by the way, that brain-dead thing.....I could use a fix for that, too

Leslie Barberie
----- Bernie Deitrick wrote: ----

Leslie

As you should have, since I was brain-dead <g>

Simply change the word Range to Cells: i.e., chang

Range(65536, ....

t

Cells(65536, ....

Sorry about that
Berni

I made the code change and I'm getting Run Time Error 1004 - Method Rang

of Object_Global failed